Nested IF formula

[CLH2018]

Hi,

I have a number of nested IF formulas that aren't working and I feel like I've tried every variation but still can't craack it! Any help would be gratefully received!

SO in the cell next to the formula, I have 3 options and depending on what is selected, I then want it to generate a number.

So if 'Low' is chosen, the score is 1, if medium is chosen, the score is 2 and if high then the score is 3.

At the moment, my formula is this:

=IF(S4>="LOW","1",IF(S4>="Medium","2",IF(S4>="High","3")))

It generates correctly for Low and High but not medium! I'm not sure where I'm going wrong.

Thanks so much

 

[steve the fish]

Hi. You wouldnt say greater than "LOW", you would say equals "LOW". Remove those > symbols.

 

[Joe4]

Welcome to the Board!

Don't use >= with text, it will treat it as a sort. Since "Medium" comes after "Low" alphabetically, "Medium" meets the first criteria (S4>="LOW").

Use

[COLOR=#333333]=IF(S4="LOW","1",IF(S4="Medium","2",IF(S4="High","3")))[/COLOR]

Also, if you want 1, 2, and 3 to come back as numbers, remove the quotes from around them.
As you have it written now, they will be returned as Text, not Numbers.

 

[CLH2018]

When I do that it just generates a FALSE without any numbers at all

 

[CLH2018]

Thanks!I've tried without the > and it just generate FALSE regardless of what's in the cell

 

[Scott Huish]

Also, you don't typically put numbers in quotes. It makes them text.

Also, instead of all those nested IFs, try a VLOOKUP:

=IFERROR(VLOOKUP(S4,{"Low",1;"Medium",2;"High",3},2,0),"")

 

[Joe4]

When I do that it just generates a FALSE without any numbers at all

That would mean that your value in S4 does not exactly equal any one of your three options.
Exactly what is in S4?
Are there are any extra spaces at the beginning or end?

 

[steve the fish]

Thats because you havent a false for the last condition. Click inside the last IF in the formula bar. Press fx button. In the dialog box for false type whatever you require.

 

[CLH2018]

Also, you don't typically put numbers in quotes. It makes them text.

Also, instead of all those nested IFs, try a VLOOKUP:

=IFERROR(VLOOKUP(S4,{"Low",1;"Medium",2;"High",3},2,0),"")

Thanks, that doesn't generate anything. The cell just goes blank weirdly.

 

[Joe4]

Thanks, that doesn't generate anything. The cell just goes blank weirdly.

These blanks and FALSEs are all telling you the same thing - your value in S4 does NOT match any of those three conditions.
Please answer the questions I asked up in post 7.