Formula Help - IF Statement...?

[AFLeddie]

Hey folks! I am trying to enter a formula into a single cell that has a few different arguments - seems easy right. The issue I am running into is that the decision is made based on criteria that fall within a range. For example:

The formula would be in cell A2 and would reference D2.

If the values in a the cell(D2) are less than or equal to 5000, enter a 1(in A2)
If the values in a the cell(D2) are greater than 5001 and less than or equal to 10000, enter a 2(in A2)
If the values in a the cell(D2) are greater than 10001 and less than or equal to 20000, enter a 3(in A2)
If the values in a the cell(D2) are greater than 20001 enter a 4(in A2)

So I was thinking something like =IF(D2<=5000,1,IF.... but coming up short...

Any help would be great!!

 

[Aladin Akyurek]

In A2 enter:

=MATCH(D2,{-9.99999999999999E+307,5001,10001,20001},1)

 

[revcanon]

You are on the right track:

=IF(D2<5001,1,IF(D2<10001,2,IF(D2<20001,3,IF(D2>20000,4))))

 

[Joe4]

Try:

=IF(D2<=5000,1,IF(D2<=10000,2,IF(D2<=20000,3,4)))

 

[AFLeddie]

Wow! Thanks for the quick responses! I was able to use the formulas - thank you!

 

[AFLeddie]

Sorry for the multiple replies...

To expand on this and I wanted to reference two cells and apply the same logic, except when they both hit the threshold. For example:

D2 = 5000
E2 = 3000

A2 would need to equal 2 since 5K and 3K fall within the <10000 range.

 

[Joe4]

To expand on this and I wanted to reference two cells and apply the same logic, except when they both hit the threshold. For example:

D2 = 5000
E2 = 3000

A2 would need to equal 2 since 5K and 3K fall within the <10000 range.

I think you will need to lay out all the scenarios and expected results in order to get a good answer.
For example, what is D2=5000 and E2=15000? What happens then?

 

[AFLeddie]

Yeah, I actually figured it out. Here is the formula that returned the result. J2 and K2 have values in them, so the sum of the two would meet each set of criteria.

=(IF((J2+K2)<5001,1,IF((J2+K2)<10001,2,IF((J2+K2)<20001,3,IF((J2+K2)>20000,4)))))

Thanks again everyone!