New Contributor

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.

Rob

5 Replies

Re: IFS Function Help

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)

Re: IFS Function Help

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?

Re: IFS Function Help

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

Re: IFS Function Help

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.

Re: IFS Function Help

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