• 411K Members
• 5,426 Online
• 466K Conversations

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

# 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"
))))))))))))``````

# 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,

# 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.

# 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.

# 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”)))))))))))

# Re: nested if statement

i figured it out, I needed to remove the paren's around the 1.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies