Formula problem (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Hans

Thank you for the explanation, I will remember it for the future.

Enjoy your day

Regards

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Hans

Thank you very much for the quick respons, I realy appreaciate it.

Enjoy the weekend

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Hans

Thank you, problem is solved

Regards

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Hans

Cell C7 cannot be replaced because that is the report date not a starting date.
Currently the formula works to report everything up to and including the report date beginning in column E.
The thing that must be adjusted is column E but still taking into account the report date.

Regards

Reply | Quote

Does this version do what you want?

=SUMPRODUCT(($E$7:$M$7>=$D8)*($E$7:$M$7<=$C$7)*($E8:$M8-$E$4:$M$4))

Reply | Quote

Hans

Thank you, that is perfect.

Amazing how logic the answer always seems after you see the perfect version.

As always I am grateful for the help, enjoy the rest of your day.

Regards

Reply | Quote

Hi

My spreadsheet as developped in the past works great, now I want to add one more feature to it:

I want to extract the latest Receipt number for each flat: We will have to look at the dates paid and then extract the matching latest receipt number for each flat.

I attach a small workbook to illustrate, any help or ideas will be appreciated.

Regards

Kobus

Reply | Quote

Enter the following formula in T8 as an array formula (confirm with Ctrl+Shift+Enter, not just Enter):

=INDEX(‘DepSlips 2’!$A$3:$A$13,MATCH(MAX(IF(‘DepSlips 2′!$D$3:$D$13=$A8,’DepSlips 2′!$B$3:$B$13)),’DepSlips 2’!$B$3:$B$13,0))

Fill down as far as needed.

Reply | Quote

Hans

Thank you for the formula, I notice that if the people pay on the same date, then the formula brings back the wrong receipt number. I add the spreadsheet to illustrate.

Regards

Kobus

Reply | Quote

Try this array formula:

=SUMPRODUCT((‘DepSlips 2’!$D$3:$D$13=$A8)*(‘DepSlips 2’!$B$3:$B$13=MAX(IF(‘DepSlips 2′!$D$3:$D$13=$A8,’DepSlips 2′!$B$3:$B$13)))*’DepSlips 2’!$A$3:$A$13)

If there is no receipt, the result will be 0, so you could use a custom format in column T that hides zeros, such as 0;;

Reply | Quote

Hans

Thank you, this one works perfectly.

Enjoy the day

Regards

Kobus

Reply | Quote

Hans

You will not believe it, but I broke the formula again.

The problem now is that when you have a case where the same person makes 2 payments on the same day then the receipts get added together. Any solution for this ocurrance?

Regards

Kobus

Reply | Quote

How do we decide which receipt to use if there are two or more on the same day?

Reply | Quote

Hans

If paid on the same day it will be the highest number since they will come from the same set of receipt numbers, so we basically need the highest number on the latest date of payment for a specific flat number.

I appologize for all the reworks but sometimes I do not think of all the possible problems that can arise beforehand.

Regards

Kobus

Reply | Quote

I just thought of another possible problem, in the case where you have a receipt numbers that are text and some numbers, then the sumproduct formula might not work also.

Reply | Quote

How do you define “highest” if the receipt “numbers” are a mixture of numbers and text?
Numerically, 20 is higher than 7, but as text, “7” is higher than “20”.

Reply | Quote

Hans,

When text number are used they will normally be in the following format:

100a
100b
100c

or a mixture of text and numbers may look like this:

102
103
104a
104b
105
106

The chance that the same customer will pay 2 times on the same date for the same flat number and get one receipt as a number and one as a text is almost impossible (we can leave that possibility out), so in the example above the text 104a and 104b might be for the same customer on the same day in which case 104b will the the last number, if it was used for 2 different customers then there is no problem since 104a and 104b will be asigned to differents flat numbers although it can be on the same day.

I hope the explanation is clear, if not please let me know

Thank you

Reply | Quote

The following array formula assumes that the Dep Slips 2 sheet will always be sorted correctly, i.e the latest receipt for a flat will always be the one with the highest row number.

=INDEX(‘DepSlips 2’!$A:$A,MAX(IF(‘DepSlips 2’!$D$3:$D$13=$A8,ROW(‘DepSlips 2’!$A$3:$A$13))))

Reply | Quote

Hans

That is correct and thank you very much, this formula works now 100% (I wanted to add “famous last words”, but a tried different configurations and it always brings back the correct answer)

So, much appreciation for the solution.

Reply | Quote