How to express range of Percentages
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 |
How to express range of Percentages
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 |
All times are GMT +1. The time now is 07:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com