How do I make the output of a cell different keywords depending on if it's a number or not?-CodePudding

O9 contains either a number or a word.

I have made this formula

=IF(ISNUMBER(O9),IF(O9<=74),"Alpha",IF(O9>=75),"Bravo"),IF(AND(O9<=65,M9="C","Charlie",IF(AND(O9<=65,M9="D","Delta",IF(AND(O9<=65,M9="E","Echo"

What I wanted to happen was, if the contents of O9 is a number, the output would be either Alpha (if O9<=74) or Bravo(if O9>=75). If O9's contents are not a number, the output would either be Charlie, Delta, or Echo.

I think I may have misplaced some commas or parentheses and made mistakes on them which made my false value (for the "IF(ISNUMBER)" ) unidentifiable.

This is the error message:

There's a problem with this formula.

If I try editing the parentheses placement, the error message says

There are too many arguments.

New to Excel so, sorry for the question. Not really aware of other formulas/commands other than the basic ones (sum, average, etc.) Thank you in advance to those who will answer!

CodePudding user response：

If you're using Office365 then you might want to try =IFS(...), in which you enter logical conditions followed by their outputs (as many condition-output pairs as you like) and it gives the output for the first condition that's true.

In this case that would probably be:

=IFS(M9="C", "Charlie", M9="D", "Delta", M9="E", "Echo", O9<=74, "Alpha", O9>74, "Bravo", 1=1, "Error")

The last two arguments make sure that if none of the previous possibilities are triggered it will say 'Error'.

NB that this won't work on some versions of excel - give it a try and see.

CodePudding user response：

Break it down into 3 steps. First step "is it a number?"

=IF(ISNUMBER(O9), IS_A_NUMBER, NOT_A_NUMBER)

Next step, for IS_A_NUMBER, "is less-than 75?" (This will ensure that number between 74 and 75 give a value — such as 74.1 — and eliminates the need to both <=74 and >=75 as separate conditions)

IF(O9<75,"Alpha","Bravo")

Put those steps together:

=IF(ISNUMBER(O9), IF(O9<75,"Alpha","Bravo"), NOT_A_NUMBER)

Finally, match "C", "D" and "E" to "Charlie", "Delta" and "Echo" for NOT_A_NUMBER:

VLOOKUP(O9, {"C", "Charlie"; "D", "Delta"; "E", "Echo"}, 2, False)

And put everything together:

=IF(ISNUMBER(O9), IF(O9<75,"Alpha","Bravo"), VLOOKUP(O9, {"C", "Charlie"; "D", "Delta"; "E", "Echo"}, 2, False))