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 ...
excel
Formulas and Functions
office 365
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"
)
  • Razorbackrob's avatar
    Razorbackrob
    Copper Contributor
    Apr 16, 2021

    SergeiBaklan 

    Thanks, but unfortunately I still have the same error only now it displays the text answer instead of NA.

    Maybe I need to use a nested IF instead, I think the problem lies there... as for some reason i can't get it to display the result of the Logical test 2. 

     

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

      Razorbackrob 

      Instead of text 

      "here is if nothing of above works"

      you shall insert formula which returns desired result. If you'd like to return exactly the same result independently of  B162="GB12" returns TRUE of FALSE, when

      =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)
       )
    ),
TRUE,
    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)
       )
    )
)

Resources