Apr 16 2021 03:57 AM
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
Apr 16 2021 04:12 AM
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)
Apr 16 2021 04:16 AM
Apr 16 2021 04:38 AM
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"
)
Apr 16 2021 05:16 AM
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.
Apr 16 2021 07:01 AM
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)
)
)
)