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! 

DanGreene's avatar
DanGreene
Copper Contributor
Apr 22, 2019

Celia_Alves

 

You can think of this has a flow chart type of analysis.  G5, H5 and I5 are prepopulated with HIGH, MODERATE, LOW.

The logic I'm attempting is a flowchart type  layout:

IF G5 LOW AND H5 LOW then Look in cell I5 for value and place the "T", "NT", or OT" in E5

In this case G5 and H5 are LOW and use the following formula to pick which (T, OT, NT) to place in E5 

IF (I5="LOW", "T", IF(I5="HIGH", "T", "T"))

Since LOW is in cell I5 , it results in "T" in E5.

 

Depending on values in G5 and H5...(Low, Low or Moderate,High or High,High...ect) it changes how the Value in I5 is interpreted to place the T, OT, or NT in E5.  Sometimes it will seem redundant, but the type of training  will cover multiple scenarios.

 

I hope Im explaining it correctly.

thank you

 

 

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! 

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

    Celia_Alves 

     

    thank you Celia!  This worked great!  I was able to populate what I needed and used the CSE to enter in the formulas!

    thanks again to everyone for all their help!!

     

     

    • Celia_Alves's avatar
      Celia_Alves
      MVP
      Apr 23, 2019
      Glad to hear you found a solution that works for you, DanGreene! Happy that I could help. There were other valid solutions in this thread also.
      Good luck with your project. :-)
  • DanGreene's avatar
    DanGreene
    Copper Contributor
    Apr 22, 2019

    Celia_Alves 

     

    thank you Celia,

    I haven't worked with arrays before, Ill see if I can implement them in somehow.

    Ill have to think on this one for a bit.

     

    thank you for all the wonderful help!

  • 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

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

Resources