Excel Formula - formula based on if cell contains

[Gareth Vincent]

Hi all,

Seeing if any of you would be assist, I am slowly learning some advance formulas in Excel and wanted to see if the following was possible.

If A1 = Yes and B1 = Yes, then C1 would be “N/A”
If A1 = Yes and B1 = No, then C1 would be “P3”
If A1 = No and B1 = No, then C1 would be “P2”

Your help would be muchly appreciated as I get a grips to more advanced formulas as I have just learnt how to use IF formulas :)

Many thanks
GV

[#2]

Hi Gareth

I'm sure someone will probably have a better way to do this but this should work:

=IFERROR(IFS(AND(A1="Yes",B1="No"), "P3", AND(A1="Yes",B1="Yes"), "N/A", AND(A1="No",B1="No"), "P2"), "Not a valid combo")

I've used IFS for multiple conditions and have also added  IFERROR to catch any invalid combos or entries.

Hope that helps. Sample file is attached.

Cheers

Damien

[#3]

Hi Gareth,

Please use this formula:

=IF(AND(A1="Yes",B1="Yes"),"N/A",
 IF(AND(A1="Yes",B1="No"),"P3",
 IF(AND(A1="No",B1="No"),"P2","")))

Hi @Damien Rosario

Your formula works just fine, but IFS function isn't available in all versions of Excel!

It currently available only for Office 365 subscribers.

Regards

[#4]

Hi Haytham

You make a good call out and Gareth has two different ways to do the same thing for his learning benefit.

Great team effort!

Cheers
Damien