formula help

[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?

 

[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)

 

[tom]

thanks!

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)