IFS Function Help

[Razorbackrob]

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 

[#2]

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

[#3]

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?

[#4]

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

[#5]

@Sergei Baklan 

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. 

[#6]

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