Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Star Lounger
    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.
    Attached Files Attached Files

  3. #3
    Star Lounger
    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  5. #5
    Star Lounger
    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

  6. #6
    Star Lounger
    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 Feb-07 then the formula must automatically based the calculation on the previous month in this case Jan-07 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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  8. #8
    Star Lounger
    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

  9. #9
    Star Lounger
    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 Aug-2007

    Regards

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  11. #11
    Star Lounger
    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 Aug-07

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula problem (Excel 2003)

    You should enter the report date as a date, e.g. 01-Aug-2007, and format the cell as mmm-yy. 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.

  13. #13
    Star Lounger
    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

  14. #14
    Star Lounger
    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

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •