Forum Discussion

MarieDR's avatar
MarieDR
Copper Contributor
Sep 27, 2019

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,...
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 27, 2019

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

 

MarieDR's avatar
MarieDR
Copper Contributor
Sep 27, 2019

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

 

Thank  you, 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 27, 2019

    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.

    • MarieDR's avatar
      MarieDR
      Copper Contributor
      Sep 29, 2019

      SergeiBaklan 

       

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

       

       

      • MarieDR's avatar
        MarieDR
        Copper Contributor
        Sep 29, 2019
        i figured it out, I needed to remove the paren's around the 1.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 27, 2019

    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.

Resources