Need help to design a monthly summary of payments in my spreadsheet

[djpingpong]

Hi,

I have a problem which may be a bit too complex for me to handle in Excel. I'm usually pretty good, so for me to ask for help means this should be a challenging question

Let's begin....

Current Situation:

/*SAMPLE SPREADSHEET*/
DATE: PAYMENT: MonthElapsed
2007/1/1 $100 2
2007/3/1 $150 1
2007/4/1 $100 7
2007/11/1 $200 2

I keep a master spreadsheet at work keeping track of payments made by our clients. The client were billed monthly and the old way is pretty straight-forward... they pay an amount of the 1st of a month and if the rate changes for another month, I would create a record to indicate the rate change with the months elapsed between records
By looking at my example, you can see that the payment for ClientX changed 4 times in 2007. The annual total is done by [$100 x 2mths] + [$150 x 1mth] + [$100 x 7mths] + [$200 x 2mths] = $1450 GRAND TOTAL for 2007... easy, right?

Well.. now we have a new client which is causing me a pain in my ass....

New Situation:
Our one client (and prob. more clients in the future) is being billed once.. MAYBE twice... per month. Our billing dates are no longer just the 1st, it's the 16th also. Before you start asking "why?", the reason is because some of the clients were paying on-time each month, so we wanted to ensure that we make them pay more frequently at smaller amounts. Hence, my new spreadsheet looks like the following

/*SAMPLE SPREADSHEET*/
DATE: PAYMENT:
2007/1/1 $50
2007/3/16 $70
2007/4/1 $50
2007/6/16 $30
2007/8/16 $40
2007/10/1 $50
2007/11/16 $100

Because the payments are doubled now, I can no longer use my "months elapsed" field. The manual method to calculate the total would be: [$50 x 5paymts] + [$70 x 1paymt] + [$50 x 5paymts] + [$30 x 4paymts] + [$40 x 3paymts] + [$50 x 3paymts] + [$100 x 3paymts] = $1260 GRAND TOTAL for 2007

I hope you follow my NEW situation... because my obvious question is "NOW WHAT DO I DO?" I'm not limited to just having to use 3 columns in excel... I can hold "temp" values as much as I need to... but there's too much logic for me to figure out the # of payments between the 2 payment records

Can anyone help? I'd love it if you can provide a sample spreadsheet with the formulas... but I won't ask anyone to do the work for me.... just a simple guidance in the proper direction or any method suggestions, I AM ALL EARS!!!

NOTE: Just an FYI... this is just part one of my problems... not only do I have to keep an annual total, I would also need an OVERALL TOTAL because the data spans over many years... but that's a problem for another day... let's handle one thing at a time

thank you all for reading this question,

cheers

 

[Bong]

Why isn't the value you're looking for just the sum of the PAYMENT column?

_________________
Bob Rashkin

 

[djpingpong]

because the SUM of the PAYMENT column doesn't take into account how many payments are paid for each value....

In the example I gave, the SUM(payment) equals only $390, but the Total I'm looking for is $1260 (as indicated in bold)

As a note to whoever else is going to read this... the most difficulty I'm dealing with are the mid-month rate changes
Any payments changes on the 16th of a month is causing me a headache.... The reason why is because the method I'm doing is to try to get a total for each month, but i'm not having any success

 

[SkipVought]

"I can no longer use my "months elapsed" field"

Why not?

If it was 100 for 2 months (periods), now it would be 50 for 4 periods. So instead of having 12 periods, you would have 24 (half-month periods)

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[djpingpong]

what's if it's a mid-month change...

For example:
2007/4/1 $50
2007/6/16 $30
2007/8/16 $40
2007/10/1 $50

How do I determine that it's $50 for 5 periods, $30 for 4 period, $40 for 3 periods, etc?

 

[ck1999]

How about this formula

=IF(DAY(F7)=16,(IF(DAY(F6)=1,((MONTH(F7)-MONTH(F6))*2)+1,(MONTH(F7)-MONTH(F6))*2)),((MONTH(F7)-MONTH(F6))*2-1))

where column F is your dates. This would give you number of payments calculated in another column

then you can multiply payment * paymentqty

ck1999

 

[djpingpong]

thanks for the formula ck1999

=IF(DAY(F7)=16,(IF(DAY(F6)=1,((MONTH(F7)-MONTH(F6))*2)+1,(MONTH(F7)-MONTH(F6))*2)),((MONTH(F7)-MONTH(F6))*2-1))

this code does work to calculate current year...
Now as i mention initially, not only do I need a column to keep track to current year total, but I also have a seperate column to keep track of TOTAL payment...

i'll try to tweak it a bit to see if I can figure it out... but if anyone would like to try, i'd very much appreciate that...

thanks for all the quick responses everybody... i love this forum

cheers

 

[ck1999]

I was messing with the formula and had to embedd another if statement try

=IF(DAY(F5)=16,(IF(DAY(F4)=1,((MONTH(F5)-MONTH(F4))*2)+1,(MONTH(F5)-MONTH(F4))*2)),(IF(DAY(F4)=1,((MONTH(F5)-MONTH(F4))*2),(MONTH(F5)-MONTH(F4))*2)))

This formula only works for days and months. It does not take into consideration the years. So if the date in f5 was 2/01/07 and f4 was 11/01/06 you get -18 and not 6.

This also will not work for the last set of payments. You will get a negative number. For the last payment qty you will need to do what skip mentioned and use a formula of =24-sum(Range of Payment qty for year)
You can also use =year(f6) in a column and then use data subtotals. The however I think is for manipulating old data instead of current data.

Would not total payments just be a sumation of payments qty
using something like =SUMPRODUCT(G4:G10,H4:H10) (col g = payment qty , col H = payment amount)

Again this equation is for past data since you do not know how may rows each year will take up.
I believe there is a way to find last row in a list however I have not used it in an excel formula but only in VBA and am not sure how.

Hope this helps

ck1999