Tiered Calculation

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

Derek,

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

HTH,
Ryan

"Derek Borckmann" wrote:

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

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

"Derek Borckmann" <djborckmann@yahoo.com> wrote in message
news:gn7Pf.14232$iR1.14225@tornado.rdc-kc.rr.com...
> 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(?
>
>