How to express range of Percentages

[nander]

Inventory Error Rate Penalty
0-1.0% 0.00%
1.0-1.5% 5.00%
1.5-2.0% 10.00%
2.0-2.5% 15.00%
2.50%+ 20.00%

When my inventory is off by a percentage of sales my managers get a penalty based on the error rate. How do I express the listed ranges above in a formula so that given a certain error rate the penalty is calculated. For example a Manager's year end bonus is $5,000. The error rate is 1.25% therefore he would be penalized 5% or $250.00.

[Bob Phillips]

Put this data ina table, say M1:N20

0 0
1 0.05
1.5 0.1
2 0.15
2.5 0.2






and then use this formula to get the penalty rate

=VLOOKUP(D1,M1:N20,2)

assuming that D1 holds the inventory

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"nander" <[email protected]> wrote in
message news:[email protected]...
>
> Inventory Error Rate Penalty
> 0-1.0% 0.00%
> 1.0-1.5% 5.00%
> 1.5-2.0% 10.00%
> 2.0-2.5% 15.00%
> 2.50%+ 20.00%
>
> When my inventory is off by a percentage of sales my managers get a
> penalty based on the error rate. How do I express the listed ranges
> above in a formula so that given a certain error rate the penalty is
> calculated. For example a Manager's year end bonus is $5,000. The error
> rate is 1.25% therefore he would be penalized 5% or $250.00.
>
>
> --
> nander
> ------------------------------------------------------------------------
> nander's Profile:
http://www.excelforum.com/member.php...fo&userid=6156
> View this thread: http://www.excelforum.com/showthread...hreadid=505406
>