Forum Discussion

Razorbackrob's avatar
Razorbackrob
Copper Contributor
Apr 16, 2021

IFS Function Help

Hi,

I am trying to do an IFS function within a sheet but if the first argument is FALSE then I would like it to carry over to Logical test 2. Here is a few photos of the problem:

So if Logical test 1 is true:

If Logcal test 1 is False then it returns N/A when I would like it to show the results of Logical Test 2, is there a way around this? 5.063 is the correct answer to show. 

 

Thanks in advance! 

Rob 

 

excel
Formulas and Functions
office 365

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 16, 2021

    Razorbackrob 

    IFS() stops formula evaluation on the first TRUE condition and ignores the rest. You may use something like

    =IFS(test1, result1, TRUE, result if test1 failed)

    • Razorbackrob's avatar
      Razorbackrob
      Copper Contributor
      Apr 16, 2021
      Thanks Sergei,

      How would I enter that into Excel?
      My current formula is:

      =IFS(B162="GB16",IF(OR(O162="311295",O162="311296",O162="311297",O162="311299",O162="311293"),Method!$G$35,INDEX(Method!$A$34:$H$34,MATCH('Own Prod'!AG162,Method!$A$34,0),IF('Own Prod'!A162="Burnt",7,8))),B162="GB12",IF(OR(O162="CL01"),Method!$G$30,INDEX(Method!$A$29:$H$29,MATCH('Own Prod'!AG162,Method!$A$29,0),IF('Own Prod'!A162="Unburnt",8,7))))

      Would i be best deleting Test 2 and entering the word True at the end of Test one?

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 16, 2021

        Razorbackrob 

        I'm not sure what exactly you'd like to calculate, but that could be like

        =IFS(
B162="GB16",
    IF(OR(O162="311295",O162="311296",O162="311297",O162="311299",O162="311293"),
       Method!$G$35,
       INDEX(Method!$A$34:$H$34,
             MATCH('Own Prod'!AG162,Method!$A$34,0),
             IF('Own Prod'!A162="Burnt",7,8)
       )
    ),
B162="GB12",
    IF(OR(O162="CL01"),
       Method!$G$30,
       INDEX(Method!$A$29:$H$29,
             MATCH('Own Prod'!AG162,Method!$A$29,0),
             IF('Own Prod'!A162="Unburnt",8,7)
       )
    ),
TRUE,
    "here is if nothing of above works"
)

Resources