Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to express range of Percentages
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Help with using range names in sum function | Excel Worksheet Functions | |||
Array to named range conversion... | Excel Discussion (Misc queries) | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel |