Forum Discussion

DanGreene's avatar
DanGreene
Copper Contributor
Apr 22, 2019
Solved

Nested IF formula in Excel 365

Hello, Im having problems getting the right syntax for nexting IF statements. Basically Im comparing values already placed into cells G5, H5 and I5 to return a value placed into cell E5. This is th...
Formulas and Functions
  • Celia_Alves's avatar
    Celia_Alves
    Apr 22, 2019

    DanGreene,

    please see screenshot and file attached.

    The formula that I used is an array formula. To make the formula to work, you need to do CTRL+SHIFT+ENTER after you finish writing it or after you edit it in the formula bar. The formula will get curly brackets at the beginning and ending after you hit those three keys.

    Good luck! 

Celia_Alves's avatar
Celia_Alves
MVP
Apr 22, 2019

DanGreene,

please see screenshot and file attached.

The formula that I used is an array formula. To make the formula to work, you need to do CTRL+SHIFT+ENTER after you finish writing it or after you edit it in the formula bar. The formula will get curly brackets at the beginning and ending after you hit those three keys.

Good luck! 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 22, 2019

Celia_Alves ,

 

By the way, to simplify the maintenance you may avoid CSE transforming the formula to

=INDEX($H$13:$H$30,MATCH(1,INDEX(($E$13:$E$30=$F6)*($F$13:$F$30=$G6)*($G$13:$G$30=$H6),0),0))

And if the matrix is correct, equivalent nested IF could be

=IF(F6="LOW",
   IF(G6="LOW",
      "T",
      IF(H6="LOW","T","NT")),
   IF(F6="MODERATE",
      IF(G6="LOW",
         IF(H6="LOW","T","NT"),
         IF(H6="HIGH","T","OT")),
      IF(G6="LOW",
         IF(H6="HIGH","NT","T"),
         IF(H6="HIGH","T","OT"))
))

or so

 

  • DanGreene's avatar
    DanGreene
    Copper Contributor
    Apr 22, 2019

    SergeiBaklan 

     

    I stepped away from my desk for a while.  Such wonderful responses.  Thank you all!

    I think I am making headway.

    I am placing:

    =IF(G5="LOW",IF(H5="LOW","T",IF(G5="LOW",IF(H5="HIGH", IF(I5="LOW","T","NT"))))), IF(G5="MODERATE",IF(H5="LOW",IF(I5="LOW","T","NT"))), IF(G5="MODERATE",IF(H5="HIGH",IF(I5="HIGH","T","OT"))), IF(G5="HIGH",IF(H5="LOW",IF(I5="HIGH","NT","T"))), IF(G5="HIGH",IF(H5="HIGH",IF(I5="HIGH","T","OT")))

     

    in the E5 cell to attempt to accommodate the "moderate" entries that could appear in the I5 column.  However I am getting a #VALUE! error.

    I think my syntax of too many IF statements perhaps?  or is there a limit on how many you can place in one formula?

     

    thank you

    dan

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 23, 2019

      DanGreene 

       

      Hi Dan,

       

      If format a bit your formula it'll be

      =IF(G5="LOW",
   IF(H5="LOW","T",
      IF(G5="LOW",
         IF(H5="HIGH",
            IF(I5="LOW","T","NT"))))), IF(G5="MODERATE",IF(H5="LOW",IF(I5="LOW","T","NT"))), IF(G5="MODERATE",IF(H5="HIGH",IF(I5="HIGH","T","OT"))), IF(G5="HIGH",IF(H5="LOW",IF(I5="HIGH","NT","T"))), IF(G5="HIGH",IF(H5="HIGH",IF(I5="HIGH","T","OT")))

      with brackets in red you close it, all after the comma gives incorrect syntax

      More correct will be

      =IF(G5="LOW",
   IF(H5="LOW",
      "T",
      IF(I5="LOW","T","NT")),
   IF(G5="MODERATE",
      IF(H5="LOW",
         IF(I5="LOW","T","NT"),
         IF(I5="HIGH","T","OT")),
      IF(H5="LOW",
         IF(I5="HIGH","NT","T"),
         IF(I5="HIGH","T","OT"))
))

      and in attached file

      • DanGreene's avatar
        DanGreene
        Copper Contributor
        Apr 23, 2019

        SergeiBaklan 

         

        Thank you Sergei!

         

        This works a treat!!!  I am new to excel formulas, and I have learned so much.

         

        Thanks again!!

         

         

         

  • Celia_Alves's avatar
    Celia_Alves
    MVP
    Apr 22, 2019
    That's right, SergeiBaklan! That's a very nice one too. Thanks!
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 22, 2019

      Celia_Alves , thank you, but nested IF here is only to demonsrate how it looks like. It has no sence in this case - formulas which transform score numbers into text levels are under question. Even if assume they are correct this logic could be changed from time to time. Nested IF with hardcoded conditions is not the a good solution from maintenance point of view. 

      • Celia_Alves's avatar
        Celia_Alves
        MVP
        Apr 22, 2019

        SergeiBaklan 

        I agree. I believe I was referring to the formula version that you presented that is free from CSE. ;-)

        Cheers!

Resources