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

Take advantage of the way the VLookup formula works. The last criteria of
the formula asks whether you want and exact match or not. If you say TRUE,
it returns the value that is as close to your number as possible, without
going over.

=VLOOKUP(error_rate,$A$1:$C$5,2,TRUE).......So, lets say the error rate is
1.55%, it will return 10%. 2.49% will be 15%, 2.50000000001 will be 20%

error rate penalty
0.0% 0.0%
1.0% 5.0%
1.5% 10.0%
2.0% 15.0%
2.5% 20.0%




"nander" wrote:


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