formula help

T

tom

I'm trying to set up liquidated damages for some purchases. The criteria for
days late is as follows:
days late 1-7 is $1000/day, then days 8-14 are $2500/day, days over 14 are
$5000/day.
I currently have the following formula which is accurate for days 1-7 only:
=IF(AND(G26>0, G26<=7),1000,IF(AND(G26>7, G26<=14),2500,IF(G26>14,5000)))*G26
The problem is beyond that, my forumula will calculate all days within the
next range. for example is something is 10 days late, it will calculate
10x$2500 instead of 7x$1000 + 3x$2500. Can anyone set this forumula
correctly?
 
J

Jacob Skaria

One way .. With number of days in cell A1

=MIN(7,A1)*1000+MAX(0,MIN(7,A1-7)*2500)+MAX(0,MIN(A1-14)*5000)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top