Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|