Calculating Bonus based on a scale with levels

[FernTurpin]

OK, so I am trying to build a pay plan for an employee.

The SCALE is based on total gross generated and each gross level has a bonus attached to it. (See below)

Gross BONUS
20,000.00 $0
25,000.00 $500
30,000.00 $1000
35,000.00 $1500
40,000.00 $2000
45,000.00 $2500
50,000.00 $3000
55,000.00 $3500
60,000.00 $4000
65,000.00 $4500
70,000.00 $5000

Then I have the year 2014 laid out in months
Jan Feb Mar Apr May Jun
48336 92501 34935 47820 89338 79095

I want to be able to calculate the bonus for each month based on the scale above, but NOT use a compounded IF statement.

I need to be able to adjust the scale and see different possible outcomes to this pay plan. :confused:

[JBeaucaire]

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:

Please Login or Register  to view this content.

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)

[FernTurpin]

OK, my error, and yes you are correct.
So.. the scale should have looked like this

Column A Column B Column C
1 Start End Bonus
2 0 19,999.99 0
3 20,000 24,999.99 500
4 25,000.00 29,999.99 1000
5 30,000.00 34,999.99 1500
6 35,000.00 39,999.99 2000
7 40,000.00 44,999.99 2500
8 45,000.00 49,999.99 3000
9 50,000.00 54,999.99 3500
10 55,000.00 59,999.99 4000
11 60,000.00 64,999.99 4500
12 65,000.00 69,999.99 5000
13 70,000.00 5500

So, what would the lookup formula look like? If we assume that the Actual Gross calc from January was in cell AA100

[FernTurpin]

ADDITIONAL - What do I do for the final amount? (Anything GREATER than $70,000 should yield $5,500)

[JBeaucaire]

My table from post #2 is all you need. There is no need for an END column, it is implied by the start of each tier. The final tier covers all amounts from there and above.

Looks like you may want to adjust the formula i suggested to reference AA100 instead of D1.
You could even move the table to a separate (hidden) sheet.