formula help
 

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(G260, G26<=7),1000,IF(AND(G267, G26<=14),2500,IF(G2614,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?

formula help
 

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)

--
Jacob


"tom" wrote:

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(G260, G26<=7),1000,IF(AND(G267, G26<=14),2500,IF(G2614,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?

formula help
 

thanks!

"Jacob Skaria" wrote:

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)

--
Jacob


"tom" wrote:

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(G260, G26<=7),1000,IF(AND(G267, G26<=14),2500,IF(G2614,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?