Need help with Excel formula

February 28, 2009 at 08:48:48
Specs: Windows XP
I'm creating an excel worksheet for some rental properties. Sometimes, the renters will pay less than what their monthly rate is, and will receive a $15 late fee if the total is not received by the 15th of the month. A lot of the renters will make partial payments throughout the month, but again, the total must be on time. This is what I have for instance, for January 2009:
B4 - total monthly rent
D4 - date received
E4- amount
G4 - date received
H4 - amount
J4 - date received
K4 - amount
M4 - late fee ($15)

What I want the formula to do is say: If D4 and G4 and J4 are greater than 1/15/09 AND E4 + H4 + K4 are less than B4, then add $15 in cell M4. In other words if total amount received does not equal the total amount due by the due date, they are charged a late fee.



See More: Need help with Excel formula

Report •


#1
February 28, 2009 at 12:34:10
First, when you say add $15 in cell M4 I assume you mean M4 should show $15. Otherwise I don't know what you are adding it to.

Next, re: If D4 and G4 and J4 are greater than 1/15/09.

I don't think that's what you really want. What if D4 and G4 are less than 1/15/09 but J4 is greater? Based on your wording, you don't want to see $15 in M4 unless all 3 dates are greater than 1/15/09.

Would simply checking to see if "today" is greater than the 15th and the SUM is less than the Total Due work for you?

=IF(AND(TODAY()>DATEVALUE("1/15/09"),SUM(E4,H4,K4)<B4),"$15","")


Report •

#2
March 1, 2009 at 10:37:45
Thank you for your post and helping me with my worksheet. I have tried your formula. It works if the total payment(s) received are less than the total due, however if I enter that a renter paid the total rent due but past the due date of 1/15/09, it doesn't show $15 in M4.

Report •

#3
March 1, 2009 at 12:43:22
I kind of thought you were going to say that...

Try this:

=IF(OR(D4>DATEVALUE("1/15/09"),G4>DATEVALUE("1/15/09"),J4>DATEVALUE("1/15/09")),"$15",IF(AND(TODAY()>DATEVALUE("1/15/09"),SUM(E4,H4,K4)<B4),"$15",""))

What happens if they make more than 3 payments?


Report •

Related Solutions

#4
March 1, 2009 at 13:48:32
That works! I hope you aren't sick of me yet, because I have another question. (Sorry, I'm taking an excel class but I haven't gotten that far yet. ) Ok, in cell P4, I want to show the balance left over after they make payments. That is, if they don't pay the full rent, like a lot of them do, they will carry a balance over. So, in P4, I have the formula saying total rent minus all of the payments plus any late charge = the balance. That formula worked before I plugged in the formula you gave me. Now I get a #value! error. However, if M4 shows 15, then the formula works. If it doesn't have a value, then the error. As for you question about making more than 3 payments, they don't usually, but I guess if that ever happens, I'll have to add more columns or add it to a previous payment.

Report •

#5
March 1, 2009 at 17:40:09
The problem is that the IF statement puts a "" (empty cell) in M4 if here is no late fee due. When you try to perform a mathematical operation with that cell, you get the #VALUE error.

Try this one. I've replaced the "" with a 0. I also replaced the "$15" with just 15 so that everything will be numbers.

=IF(OR(D4>DATEVALUE("1/15/09"),G4>DATEVALUE("1/15/09"),J4>DATEVALUE("1/15/09")),15,IF(AND(TODAY()>DATEVALUE("1/15/09"),SUM(E4,H4,K4)<B4),15,0))

BTW... you should select the formula and use Tools...Formula Auditing...Evaluate Formula to single step through the formula with various dates and amounts in the cells. This will help you understand how the formula works. Then you should be able to impress your teacher. :)


Report •

#6
March 2, 2009 at 05:19:52
Thanks so much! That worked and you've been VERY helpful. Don't know about impressing anyone, especially since you did the hard work, but it always feels good when you can get something to work the way you want it to. Thanks again!!!! Lou

Report •


Ask Question