• 669K Members
• 10.1K Online
• 825K Conversations

Highlighted
New Contributor

# nested if statement

I wrote the below nested if formula, but I'm getting no results.  Could someone please take a look at it and tell me what I've done wrong or what's missing?

'= if(O2<19,239,"A",if(O2>19,240, "B",if(O2> 24,440, "C",if(O2>30,680, "D",if(O2>39,000, "E",if(O2>49,920, "F",if(O2>62,920, "G",if(O2>80,080, "H",if(O2>101,920, "I",if(O2>128960, "J",if(O2>163,800, "K",if(O2>208,000, "L"))))))))))))

6 Replies
Highlighted

# Re: nested if statement

Perhaps you mean

``````=
if(O2<19,239,"A",
if(O2<24,440,"B",
if(O2<30,680,"C",
if(O2<39,000,"D",
if(O2<49,920,"E",
if(O2<62,920,"F",
if(O2<80,080,"G",
if(O2<101,920,"H",
if(O2<128,960,"I",
if(O2<163,800,"J",
if(O2<208,000,"K","L"
))))))))))))``````

Highlighted

# Re: nested if statement

@Sergei Baklan  - No, that changed the formula completely.  The results I require must come from the  greater than number.

Thank  you,

Highlighted

# Re: nested if statement

IMHO, that changes formula but not result. For example, if for O2 between 19240 and 24440 formula shall return "B", you may use

...IF(O2>19240,"B",IF(O2>24440,"C"...

but exactly the same result will be with

...IF(O2<=19240,"A",IF(O2<=24440,"B",...

It doesn't matter in which direction criteria check.

Highlighted

# Re: nested if statement

And alternatively you may use formula like

``````=LOOKUP(O2,
{-1000,19239,24440,30680,39000,49920,62920,80080,101920,128960,163800,208000},
{"A","B","C","D","E","F","G","H","I","J","K","L"}
)``````

Of course, it's better not to hardcode constants but put in some ranges.

Highlighted

# Re: nested if statement

I reversed the numbers and the below actually worked except for the "1" position.  Not sure why it's not  returning that information,

= IF(O9>208000, "12",IF(O9>163800, "11",IF(O9>128960, "10",IF(O9>101920, "9",IF(O9>80080, "8",IF(O9>62920, "7",IF(O9>49920, "6",IF(O9>39000, "5",IF(O9>30680, "4",IF(O9> 24440, "3",IF(O9>19240, "2",”1”)))))))))))

Highlighted

# Re: nested if statement

i figured it out, I needed to remove the paren's around the 1.