Search code examples
excelif-statementifs

I want to check a IF formula by using the IFS function

I have the following IF function, which tells us the source of some data in Excel.

=IF(D168=T168, " ", IF(AND(S168=0, R168<>0), "Invalid number", IF(AND(R168=0, Q168<>0),"Invalid Text", IF(AND(D168=0, T168<>0), "Source X", IF(AND(T168<>0, T168<>F168, OR (T168=G168, T168=O168, T168=P168)), "Source Y", " ")))))

Now I want to check that this formula is pulling the right source with an IFS function. I have tried IFS(D168=T168, " ", S168=0 R168<>0, "Invalid Number") but this formula returns an error.

I want to basically translate the IF formula above into an IFS formula.

Solution

  • The syntax for the IFS function in Microsoft Excel is:

    =IFS( condition1, return1 [,condition2, return2] ... [,condition127, return127] )

    So after S=168, what value?

    And don't forget the commas!

    You probably omitted the AND. Because that is your 'condition 2'. i.e. something that will return a boolean true or false result.

    AND(S168=0, R168<>0)

    Leading to:

    =IFS(D168=T168, " ", AND(S168=0, R168<>0), "Invalid Number")

    etc, etc.

    Let me know if that works for you.