CODEWITHSUNDEEP
excelexcel-formula
marchemike
marchemike

Reputation: 3277

Using Switch Formula on #N/A Values

I'm trying to create a switch formula in excel where in if the value of a cell is greater than 6 it will show as "less than 6 months", whereas if the value of the cell is greater than 6 months and less than 12 it will show "Less than 1 year". I also need to add that if the cell's value is #N/A it should say unknown.

=SWITCH(TRUE,R2<6,"Less than 6 months", AND(R2>=6,R2<12),"Less than 1 year",Q2=#N/A, "Unknown")

However when I do this the formula goes haywire. The cell that I compare into(R2) is from another formula and it could show numbers or the #N/A value if not applicable.

How do I make the formula work that when the value is #N/A it will show as Unknown on the switch case?

Upvotes: 0

Views: 496

Answers (1)

Juan Medina
Juan Medina

Reputation: 559

You should use IFERROR

=SWITCH(TRUE,R2<6,"Less than 6 months", AND(R2>=6,R2<12),"Less than 1 year",IFERROR(R2,"Error")="Error", "Unknown")

Also, instead of using SWITCH you could use IF

=IF(R2<6,"Less than 6 months",IF(AND(R2>=6,R2<12),"Less than 1 year",IFERROR(R2,"Unknown")))

Upvotes: 0

Related Questions