Home

nested if statement

%3CLINGO-SUB%20id%3D%22lingo-sub-879770%22%20slang%3D%22en-US%22%3Enested%20if%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879770%22%20slang%3D%22en-US%22%3E%3CP%3EI%20wrote%20the%20below%20nested%20if%20formula%2C%20but%20I'm%20getting%20no%20results.%26nbsp%3B%20Could%20someone%20please%20take%20a%20look%20at%20it%20and%20tell%20me%20what%20I've%20done%20wrong%20or%20what's%20missing%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E'%3D%20if(O2%26lt%3B19%2C239%2C%22A%22%2Cif(O2%26gt%3B19%2C240%2C%20%22B%22%2Cif(O2%26gt%3B%2024%2C440%2C%20%22C%22%2Cif(O2%26gt%3B30%2C680%2C%20%22D%22%2Cif(O2%26gt%3B39%2C000%2C%20%22E%22%2Cif(O2%26gt%3B49%2C920%2C%20%22F%22%2Cif(O2%26gt%3B62%2C920%2C%20%22G%22%2Cif(O2%26gt%3B80%2C080%2C%20%22H%22%2Cif(O2%26gt%3B101%2C920%2C%20%22I%22%2Cif(O2%26gt%3B128960%2C%20%22J%22%2Cif(O2%26gt%3B163%2C800%2C%20%22K%22%2Cif(O2%26gt%3B208%2C000%2C%20%22L%22))))))))))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-879770%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879892%22%20slang%3D%22en-US%22%3ERe%3A%20nested%20if%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879892%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416503%22%20target%3D%22_blank%22%3E%40MarieDR%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20mean%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%0Aif(O2%26lt%3B19%2C239%2C%22A%22%2C%0Aif(O2%26lt%3B24%2C440%2C%22B%22%2C%0Aif(O2%26lt%3B30%2C680%2C%22C%22%2C%0Aif(O2%26lt%3B39%2C000%2C%22D%22%2C%0Aif(O2%26lt%3B49%2C920%2C%22E%22%2C%0Aif(O2%26lt%3B62%2C920%2C%22F%22%2C%0Aif(O2%26lt%3B80%2C080%2C%22G%22%2C%0Aif(O2%26lt%3B101%2C920%2C%22H%22%2C%0Aif(O2%26lt%3B128%2C960%2C%22I%22%2C%0Aif(O2%26lt%3B163%2C800%2C%22J%22%2C%0Aif(O2%26lt%3B208%2C000%2C%22K%22%2C%22L%22%0A))))))))))))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879924%22%20slang%3D%22en-US%22%3ERe%3A%20nested%20if%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879924%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20-%20No%2C%20that%20changed%20the%20formula%20completely.%26nbsp%3B%20The%20results%20I%20require%20must%20come%20from%20the%26nbsp%3B%20greater%20than%20number.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%26nbsp%3B%20you%2C%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-880008%22%20slang%3D%22en-US%22%3ERe%3A%20nested%20if%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880008%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416503%22%20target%3D%22_blank%22%3E%40MarieDR%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%20that%20changes%20formula%20but%20not%20result.%20For%20example%2C%20if%20for%20O2%20between%2019240%20and%2024440%20formula%20shall%20return%20%22B%22%2C%20you%20may%20use%3C%2FP%3E%0A%3CP%3E...IF(O2%26gt%3B19240%2C%22B%22%2CIF(O2%26gt%3B24440%2C%22C%22...%3C%2FP%3E%0A%3CP%3Ebut%20exactly%20the%20same%20result%20will%20be%20with%3C%2FP%3E%0A%3CP%3E...IF(O2%26lt%3B%3D19240%2C%22A%22%2CIF(O2%26lt%3B%3D24440%2C%22B%22%2C...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20doesn't%20matter%20in%20which%20direction%20criteria%20check.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-880020%22%20slang%3D%22en-US%22%3ERe%3A%20nested%20if%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880020%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416503%22%20target%3D%22_blank%22%3E%40MarieDR%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20alternatively%20you%20may%20use%20formula%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DLOOKUP(O2%2C%0A%20%20%20%7B-1000%2C19239%2C24440%2C30680%2C39000%2C49920%2C62920%2C80080%2C101920%2C128960%2C163800%2C208000%7D%2C%0A%20%20%20%7B%22A%22%2C%22B%22%2C%22C%22%2C%22D%22%2C%22E%22%2C%22F%22%2C%22G%22%2C%22H%22%2C%22I%22%2C%22J%22%2C%22K%22%2C%22L%22%7D%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EOf%20course%2C%20it's%20better%20not%20to%20hardcode%20constants%20but%20put%20in%20some%20ranges.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-881567%22%20slang%3D%22en-US%22%3ERe%3A%20nested%20if%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-881567%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20reversed%20the%20numbers%20and%20the%20below%20actually%20worked%20except%20for%20the%20%221%22%20position.%26nbsp%3B%20Not%20sure%20why%20it's%20not%26nbsp%3B%20returning%20that%20information%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%20IF(O9%26gt%3B208000%2C%20%2212%22%2CIF(O9%26gt%3B163800%2C%20%2211%22%2CIF(O9%26gt%3B128960%2C%20%2210%22%2CIF(O9%26gt%3B101920%2C%20%229%22%2CIF(O9%26gt%3B80080%2C%20%228%22%2CIF(O9%26gt%3B62920%2C%20%227%22%2CIF(O9%26gt%3B49920%2C%20%226%22%2CIF(O9%26gt%3B39000%2C%20%225%22%2CIF(O9%26gt%3B30680%2C%20%224%22%2CIF(O9%26gt%3B%2024440%2C%20%223%22%2CIF(O9%26gt%3B19240%2C%20%222%22%2C%E2%80%9D1%E2%80%9D)))))))))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-881584%22%20slang%3D%22en-US%22%3ERe%3A%20nested%20if%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-881584%22%20slang%3D%22en-US%22%3Ei%20figured%20it%20out%2C%20I%20needed%20to%20remove%20the%20paren's%20around%20the%201.%3C%2FLINGO-BODY%3E
MarieDR
New Contributor

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

@MarieDR 

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

 

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

 

Thank  you, 

@MarieDR 

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.

@MarieDR 

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.

@Sergei Baklan 

 

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

 

 

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
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies