Search code examples
excelexcel-formula

Excel IF AND statement with multiple results

I would like to develop the formula based on the IF(AND) statement, which will throw more than 2 results. Usually, it's just 2 (value if true and value if false). I am just wondering if it could be possible to have something like the "nested" output values i.e. Value 1 if True, Value 2 If true within the false, and Value 3 if false within the false.

I have the calendar work and I would like to put the "%" for Saturday's column and "Cost" for Sunday's one.

enter image description here

but with the following formula:

=IF(AND(WEEKDAY(AG9,2))=7,"Cost",IF(WEEKDAY(AG9,2)=6,"%",AG9))

am just able to reproduce the second one.

enter image description here

Is there any way to have 3 output values or should I develop the same one in the separate row?

Solution

  • This works for me, OP needs to remove the AND() function. using SWITCH() looks more cleaner and neater here:

    enter image description here

    =SWITCH(WEEKDAY(A1,2),6,"%",7,"Cost",A1)

    Or,

    =IF(WEEKDAY(A1,2)=7,"Cost",IF(WEEKDAY(A1,2)=6,"%",A1))

    Or,

    =SWITCH(WEEKDAY(A1:L1,2),6,"%",7,"Cost",A1:L1)

    Note: Change cell references and ranges accordingly as per your suit.