A LOOKUP table is the thing. But your example table above may not be in the correct format. You list $20,000=0, but what about $19,999? What about $20,001?
An Excel lookup table actually would have two columns, the first lists the START of each tier and the second lists the bonus that goes with that tier. So when a second tier is entered, that automatically defines where the prior tier ended. Example:
Gross BONUS
0 $0
20,000.00 $500
25,000.00 $1000
30,000.00 $1500
35,000.00 $2000
40,000.00 $2500
45,000.00 $3000
50,000.00 $3500
55,000.00 $4000
60,000.00 $4500
65,000.00 $5000
70,000.00 $5500
In this table, a GROSS or 57,500 falls in the 55,000 tier so would generate a $4000 bonus.
Once your table is in the correct format, you can use a standard LOOKUP formula. Assuming a table in columns A:B and a gross value in D1:
=LOOKUP(D1, A:A, B:B)
LinkBack URL
About LinkBacks
Register To Reply
icon below to give reputation feedback, it is appreciated.
Bookmarks