Nested If statement formula not working for range of values

[michelle3631]

Hello - I am trying to figure out the formula for a payroll related item - i have tried several and cannot get them to work right. Basically, the value in a certain cell (let's say A1) will determine what amount of extra hourly pay someone gets. And, the logical test is a range. For example, if the value in A1 is 22% or lower, the bonus is $2.00. If the value in A1 is between 23 and 26%, the bonus is $1.50. And so on. Each logical test is a range that determines the bonus. Here is the bonus structure:

Bonus Pay

36% & over no bonus ($0)
31% to 35% $0.50
27% to 30% $1.00
23% to 26% $1.50
up to 22% $2.00

Anyone have the right formula for me? Thanks!!

[jeffreybrown]

Try this...

Data Range

	A	B	C	D	E
1	28%	$1.00	B1:=LOOKUP(A1,$D$1:$E$5)	0%	$2.00
2				23%	$1.50
3				27%	$1.00
4				31%	$0.50
5				36%	$0.00

[michelle3631]

Thank you, I'll give it a try. I was trying to tell it in the formula instead of a lookup, but let me try what you have!

[jeffreybrown]

If you want to have it inside the formula instead of referencing a table...

=LOOKUP(A1,{0;0.23;0.27;0.31;0.36},{2;1.5;1;0.5;0})

or

=IF(A1>=0.36,0,IF(A1>=0.31,0.5,IF(A1>=0.27,1,IF(A1>=0.23,1.5,IF(A1>=0,2,"")))))

[leelnich]

Or going lowest threshold to highest:
=IF(A1<23%,2,IF(A1<27%,1.5,IF(A1<31%,1,IF(A1<36%,0.5,0))))

[michelle3631]

So that's what I did, but it didn't work for me. I used your formula above, which was almost identical to mine, and I think it worked. I'll plug in some new data and make sure. Thank you so much!

[leelnich]

You're most welcome! Upon conclusion, please mark your thread as SOLVED (Thread Tools above post #1). And since you're new...
Clicking the Add Reputation star below helpful posts is a nice way to show appreciation to those who contributed. Regards - Lee