SOLVED

# Using the If Function to select a identify a catagory a specifi cell fits into

Copper Contributor

# Using the If Function to select a identify a catagory a specifi cell fits into

I have a cell with a fixed number that is entered to identify how large a company may be.  In another cell, I want to identify what the category is by using the IF function.  There are 4 categories that the value of the first sell would be selected based on a numerical test.  The numerical tests are A.  <=.3 would be Micro Cap;  B.  >.3<=2 would be a Small Cap, C.  >2<=10 would be a Mid Cap;  D.  >10<=200 would be a large cap; and E.  >200 would be a mega cap.

My problem is that when I put this criteria into an IF function, it turns up as FALSE in the cell where the IF formula is located.  This means it only looks at the first criteria but doesn't go to the next test to determine its category.  The formula I use is: =IF(B\$5<=2,"SmallCap",IF(B\$5>2<=10,"Mid Cap",IF(B\$5>10<=200,"Large Cap",IF(B\$5>200,"Mega Cap")))) where B5 is the value to be tested.  I must have the formula messed up so any help would be appreciated.  (I have intentionally left out the Micro Cap test in the formula.)

3 Replies
best response confirmed by JAKs_365 (Copper Contributor)
Solution

# Re: Using the If Function to select a identify a catagory a specifi cell fits into

Attached is the formula with example in the attached file.

``````=IF(ISBLANK(B\$5), "",
IF(B\$5<=0.3, "Micro Cap",
IF(B\$5<=2, "Small Cap",
IF(B\$5<=10, "Mid Cap",
IF(B\$5<=200, "Large Cap", "Mega Cap")
)
)
)
)``````

My answers are voluntary and without guarantee!

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

# Re: Using the If Function to select a identify a catagory a specifi cell fits into

This was really helpful and I appreciate your great help with this!!!

# Re: Using the If Function to select a identify a catagory a specifi cell fits into

As variant

``````=XLOOKUP(
B\$5,
{0.3,2,10,200,1E+99},
{"Micro Cap","Small Cap","Mid Cap","Large Cap","Mega Cap"},
"", 1)``````
1 best response

Accepted Solutions
best response confirmed by JAKs_365 (Copper Contributor)
Solution

# Re: Using the If Function to select a identify a catagory a specifi cell fits into

Attached is the formula with example in the attached file.

``````=IF(ISBLANK(B\$5), "",
IF(B\$5<=0.3, "Micro Cap",
IF(B\$5<=2, "Small Cap",
IF(B\$5<=10, "Mid Cap",
IF(B\$5<=200, "Large Cap", "Mega Cap")
)
)
)
)``````

My answers are voluntary and without guarantee!