Nested IF Statements Assistance Requested

[RKS2nd]

I am attempting to insert a number in $E3 based on 1 out of 10 text entries entered in $G3. I have created the following nested IF statement.

=IF($G3="Critically High", 10, IF($G3="extremely high", 9, IF($G3="very high", 8, IF($G3="high",7, IF($G3="medium high", 6, IF($G3="medium", 5, IF($G3="medium low", 4, IF($G3="low", 3, IF($G3="very low", 2, IF($G3="extremely low", 1,))))))))))

Unfortunately, even though I am using Excel 2013, I am receiving an error message stating "The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format"

Any assistance is greatly appreciated. If there is a better formula to accomplish the same function, I am willing to try anything.

Roy

 

[Joe4]

You are only allowed to nest up to 7 levels of IF statements (in older versions of Excel, not sure about 2013).
How about just setting up a LOOKUP table, that lists the 10 possibilities and their corresponding values, and use a VLOOKUP function?
See: MS Excel: VLOOKUP Function (WS)

 

[BobUmlas]

Use VLOOKUP -- first enter this in an available place, say J1:K10
Critically High 10
Extremely High 9
very high 8
etc
extremely low 1

Then the formula is =VLOOKUP(G3,J1:K10,2,FALSE)

And by the way - I had no error message putting your exact formula in Excel 2013.

 

[RKS2nd]

Use VLOOKUP -- first enter this in an available place, say J1:K10
Critically High 10
Extremely High 9
very high 8
etc
extremely low 1

Then the formula is =VLOOKUP(G3,J1:K10,2,FALSE)

And by the way - I had no error message putting your exact formula in Excel 2013.

I am using Excel 2013!

 

[RKS2nd]

You are only allowed to nest up to 7 levels of IF statements (in older versions of Excel, not sure about 2013).
How about just setting up a LOOKUP table, that lists the 10 possibilities and their corresponding values, and use a VLOOKUP function?
See: MS Excel: VLOOKUP Function (WS)

I have read that POST 2007, up to 64 nested IFs can be used. I am using 2013.

 

[njimack]

I am using Excel 2013!

But is it an Excel 2013 file or a pre-2007 file (i.e with the .xls extension)?

 

[RKS2nd]

I will create a separate table and use vlookup. Thanks!

 

[dreid1011]

IF's can only be nested 7 times. You could use a lookup table, but you would have to put the data on a sheet somewhere to work. With a lookup table, you can then use a formula like this:

=VLOOKUP($G1,$A$1:$B$10,2,FALSE)

Your text would go in 1 column, A1:A10 in the above formula, the numbers in a second column, B1:B10 above. Then you look for the value in G3 in the table, and it returns the corresponding value in the column you tell it.

 

[RKS2nd]

But is it an Excel 2013 file or a pre-2007 file (i.e with the .xls extension)?

It is a pre-2007 file saved as an Excel File (not as a 97-2003 file)

 

[Joe4]

It is a pre-2007 file saved as an Excel File (not as a 97-2003 file)

What exactly is the file extension?

Even though Excel 2013 may allow you to nest more than 7, I would still prefer to use VLOOKUP.
I am no fan of unnecessarily long formulas. They can be a pain to maintain!