Simple Nested IF - Won't Work

[FanofExcel18]

I can't seem to get this to work.

<=5% = 1
>5% - 10% = 2
11%=3
12%>4

=IF(AND(D3<0.05),1,IF(AND(D3>0.05),2,IF(AND(D>=0.11),3,IF(AND(D3>0.12),4))))

=IF(AND(D3<0.05),1,IF(AND(D3>=0.05),2,IF(AND(D<=0.10),2,IF(AND(D3=0.11),3,IF(AND(D3>=0.12),4)))))

 

[Fluff]

It's not entirly clear what values you should be returning & when, but try
=IF(D3<=0.05,1,IF(D3<=0.1,2,IF(D3<=0.12,3,4)))

 

[dreid1011]

First, your AND() functions aren't doing anything here but taking up space.

To use AND() to test against a range, you have to use it like this: AND(D3>.5,D3<=.1)

So, maybe something like this:

=IF(D3<=.05,1,IF(D3<=.1,2,IF(D3=.11,3,4)))

Or

=LOOKUP(D3,{0,.051,.101,.111},{1,2,3,4}) This one is untested, but I believe the lookup values are right.

 

[Joe4]

Why are you using AND? You only would use that if you have multiple conditions to check within a single IF.

Here is one way to do what you want:
=IF(D3>=0.12,4,IF(D3=0.11,3,IF(D3>.05,2,1)))

Note, however, is it is possible to have a fractional percentage between 10% and 11% or between 11% and 12%, you may get unexpected results, as you haven't seemed to account for that in your conditions.

 

[JackDanIce]

Same clarity/why questions as above and another suggestion (with assumptions made to banding) to try:

=--(D3<0.05)+--(D3>=0.05)+--(D3>=0.1)+(D3>=0.11)+--(D3>=0.12)

 

[FanofExcel18]

Why are you using AND? You only would use that if you have multiple conditions to check within a single IF.

Here is one way to do what you want:
=IF(D3>=0.12,4,IF(D3=0.11,3,IF(D3>.05,2,1)))

Note, however, is it is possible to have a fractional percentage between 10% and 11% or between 11% and 12%, you may get unexpected results, as you haven't seemed to account for that in your conditions.

This was very helpful. TBH, the AND statement was from an older formula that I pulled from online years ago. I struggle with these and appreciate the help. I don't need to worry about the Fractional numbers.

Could I ask your assistance with one more? I'm trying to apply the same logic but its just not working:
14+ =1
11-13 = 2
8-10 = 3
8<= 4

=IF(H4<8,4,IF(H4=8,3,IF(H4>10,2,1)))

1 never shows and I have plenty of ones that should.

 

[JackDanIce]

Try:

=IF(D3<8,4,--(D3>=14)+--(D3>=11)+--(D3>=8))

Note, this will return 4 if D3 is empty. If you want to avoid blank cells try:

=IF(LEN(D3),IF(D3<8,4,--(D3>=14)+--(D3>=11)+--(D3>=8)),"")

 

[Joe4]

The way to approach this is to determine the break points, and pick a direction and go up, or go down.
To follow your current logic, should look like, the break points would be at 8, 11, and 14.
So the formula would look like this:
=IF(H1<8,4,IF(H4<11,3,IF(H14<14,2,1)))

 

[Fluff]

How about

=IF(H4<=8,4,IF(H4<=10,3,IF(H4<=13,2,1)))

 

[FanofExcel18]

The way to approach this is to determine the break points, and pick a direction and go up, or go down.
To follow your current logic, should look like, the break points would be at 8, 11, and 14.
So the formula would look like this:
=IF(H1<8,4,IF(H4<11,3,IF(H14<14,2,1)))

Thank you! For this and the explanation!!!