Tiered Calculation

[Derek Borckmann]

Here is the issue. There is a tiered cost associated with hours.

Any help would be appreciated.

In column a the first 60 hours cost $100.00 per hour.
The next 100 cost 75.00 per hour.
anything after that costs 60.00 per hour.

(Column b should read = $17,400.00)

a b
Hours Charge
1 225 =sum(?

 

[Ryan Poth]

Derek,

Try:
=MIN(A1,60)*100+(MIN(A1,160)-MIN(A1,60))*75+MAX(A1-160,0)*60

HTH,
Ryan

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(A1>{0;60;160}),(A1-{0;60;160}),{100;-25;-15})

For more info, see:

http://mcgimpsey.com/excel/variablerate.html

Biff