Overdue Penalty Calculation

Hi,

Unless I'm misunderstanding this, all you need to do is replace I5 with (I5-I4) in your formula in L5, so:

Formula:

=IF(J5>0,(J5/1000)*K5*(I5-I4),0)

That gives 524.15 and, when dragged down to L6, gives 174.14 there.

If you want exactly the same formula in each row, then try this, which checks if the overdue amount (column J) is >0 on both this row and the one above:

Formula:

=IF(AND(J4>0,J5>0),(J5/1000)*K5*(I5-I4),IF(J5>0,(J5/1000)*K5*I5,0))

This gives the correct answers of 151.64, 524.15 and 174.15 in rows 4, 5 and 6, and looks to me that it gives the correct answers on rows 8-11 (11986.65, 119.38, 104.38, 350.68), row 13 (4700.31), row 15 (11946.25) and rows 17-18 (3017.83, 204.84).

Hope that does what you want.

Glad it worked for you.

I'm sure it's possible to build a template using macros, but I'm not a VBA expert so I'm not the best person to help you. I suggest you post a new thread in the VBA forum (http://www.excelforum.com/excel-programming-vba-macros/), explaining how you want the template to work / what it is you want the macros to do (for example, maybe you want to fill in a form which then fills in the data for you, or something like that). Include a link to this thread but make it clear that it's a new question. Good luck.

You're welcome.