Need help claculating multiple rates for one number

[Jboehme]

HI, I am calculating our business sick day and vacation liability. The rates are 1-100 is $25, 101-200 is $35 and 201-300 $50. Is there a formula to help me calculate these rates. Here is an example[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Days[/TD]
[TD]Amount [/TD]
[/TR]
[TR]
[TD]98[/TD]
[TD]2,450[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]11,000[/TD]
[/TR]
[TR]
[TD]201[/TD]
[TD]6,050[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]4,250[/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD]3,025[/TD]
[/TR]
</tbody>[/TABLE]
Thanks for the help

 

[steve the fish]

Try this:

=IF(A2>200,(A2-200)*50+100*35+100*25,IF(A2>100,(A2-100)*35+100*25,A2*25))

However maybe better to use cell references for the dollar amounts so they can be easily changed.

 

[Jboehme]

Try this:

=IF(A2>200,(A2-200)*50+100*35+100*25,IF(A2>100,(A2-100)*35+100*25,A2*25))

However maybe better to use cell references for the dollar amounts so they can be easily changed.

Perfect thanks!