Results 1 to 15 of 31
Thread: Formula problem (Excel 2003)

20070612, 07:38 #1
 Join Date
 Apr 2006
 Location
 Accra, Ghana
 Posts
 84
 Thanks
 0
 Thanked 0 Times in 0 Posts
Formula problem (Excel 2003)
Hello everybody
I am trying to calculate outstanding maintenace fees on a spreadsheet based on a report date.
I attach a sample spreadsheet with limited info to illustrate.
The formula should calculate fees due by adding fees as per the Monthly Fees row from the beginning of the spreadsheet, in this case column D upto and including the reporting month date as typed in. That mean if the report month is changed the answer will change automatically.
In the same way the fees paid upto the reporting month is calculated and the fees payable deducted to get the positive or negative balance.
In the attachment I added a few sample answers for clarification.
Any help will be appreciated

20070612, 08:24 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Formula problem (Excel 2003)
See attached version. I changed the way the year and month are concatenated in order to be able to compare dates.
Your sample wasn't entirely correct since the monthly fee changes.

20070612, 09:07 #3
 Join Date
 Apr 2006
 Location
 Accra, Ghana
 Posts
 84
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem (Excel 2003)
Hans
Thank you very much for the solution.
And you are correct my sample was wrong, BUT YOUR FORMULA WORKS CORRECT
As always I appreciate your help very much, just for educational purposes, what is the difference between the two date concatonations?
Regards

20070612, 09:15 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Formula problem (Excel 2003)
You coded the month as myyyy. For example, May 2006 becomes 52006. This is sufficient to compare equality of two months, but not to know whether one month is earlier or later than another one, at least not without additional calculations, take (for example) 92006, 122006 and 12007 cannot easily be sorted in the correct order, whether you see them as numbers or as text.
I used yyyymm; for May 2006 this becomes 200605. This makes it easy to compare months: 200609 < 200612 < 200701.

20070612, 09:41 #5
 Join Date
 Apr 2006
 Location
 Accra, Ghana
 Posts
 84
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem (Excel 2003)
Hans
Thank you for the explanation, I will remember it for the future.
Enjoy your day
Regards

20070622, 11:02 #6
 Join Date
 Apr 2006
 Location
 Accra, Ghana
 Posts
 84
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem (Excel 2003)
Hans
May I tap your brain a bit more on this one?
I attach the same workbook as before with a little information in.
What I am trying to do is to calculate the previous total also, so if my report date is Feb07 then the formula must automatically based the calculation on the previous month in this case Jan07 and so on. I was thinking of adding another reference cell for the calculation but do not know how to get the new cell to reduce automatically by one month.
Regards

20070622, 11:11 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Formula problem (Excel 2003)
Use this formula in K7:
=TEXT(DATE(YEAR(C3),MONTH(C3)1,1),"yyyymm")
The expression DATE(YEAR(C3),MONTH(C3)1,1) returns the first day of the previous month to that in C3. You can then use
=SUMPRODUCT(($D$7:$H$7<=$K$7)*($D$4:$H$4+$D8:$H8))
in K8 and fill down.

20070622, 11:22 #8
 Join Date
 Apr 2006
 Location
 Accra, Ghana
 Posts
 84
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem (Excel 2003)
Hans
Thank you very much for the quick respons, I realy appreaciate it.
Enjoy the weekend

20070627, 07:59 #9
 Join Date
 Apr 2006
 Location
 Accra, Ghana
 Posts
 84
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem (Excel 2003)
Hans
I was wondering, after I used the formula as stated earlier and I get an answer of 200708 which is in the format of =TEXT(AF6,"yyyymm") how can I get the display of 200708 back to displaty Aug2007
Regards

20070627, 08:14 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Formula problem (Excel 2003)
Why would you want that? The "real" dates are already available in other cells, so there should be no need to convert the text string back to a date.

20070627, 10:31 #11
 Join Date
 Apr 2006
 Location
 Accra, Ghana
 Posts
 84
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem (Excel 2003)
Hans
I am using the formulas in more than one report, so in short what I want to do in the following for example:
If I calculate how much money was paid by a certain report date I then take the overpayment/underpayment and devide that by the monthly fee to see how many months the person is paid in advance or behind.
I then add that to the report date to get for instance 200708, now for the people reading the reports it will be easier to read the result on the format of Aug07

20070627, 10:54 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Formula problem (Excel 2003)
You should enter the report date as a date, e.g. 01Aug2007, and format the cell as mmmyy. Use this in your report.
Use a formula such as =TEXT(A4,"yyyymm") to obtain the text string 200708, and use this for the calculations.
You can hide the row or column with this text value if desired.

20070627, 12:34 #13
 Join Date
 Apr 2006
 Location
 Accra, Ghana
 Posts
 84
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem (Excel 2003)
Hans
Thank you, problem is solved
Regards

20070702, 06:56 #14
 Join Date
 Apr 2006
 Location
 Accra, Ghana
 Posts
 84
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula problem (Excel 2003)
Good morning
My spreadsheet works almost perfect now save one more modification.
I attach a small extraction for easy reference and explanation.
Currently the formula in Column O refers to Column E as the starting date for all
I know I can edit each row's formula to take different stating dates as in Column C into account, but
How can the formulas in Column O be adjusted as to take different start dates as in Column C into account automatically?
Your advice will be greatly appreciated
Regards

20070702, 07:07 #15
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Formula problem (Excel 2003)
You already calculated the formatted text values of the start dates in column D, so you should be able to use
=SUMPRODUCT(($E$7:$M$7<=$D8)*($E8:$M8$E$4:$M$4))
in O8 and fill down. $C$7 has been replaced with $D8. Note that the row number is relative now because you want to use different starting dates depending on the row.