Nested IF with text and numbers

[NICKRENN]

Hi

I'm having an issue with a nested IF formula. This is the formula i'm trying to use

=IF(BI2=over_600,"0",IF(BI2=450_600,"1",IF(BI2=301_450,"2",IF(BI2=151_300,"3",IF(BI2=51_150,"4",IF(BI2=0_50,"5","0"))))))

There are 7 variables and their corresponding scores(): Blank Cells (0), over_600 (0), 450_600 (1), 301_450 (2), 151_300 (3), 51_150 (4), 0_50 (5).

Currently the column is set to General, if i change it to Number i have the same issue that Excel just gives me the error message - your formula is wrong.

The file is converted from a CSV data collection tool.

Thanks

nick

 

[AlanY]

try quoted the over_600 etc to "over_600" etc

 

[NICKRENN]

Thank you! - knew there was something i was forgetting.

using "over_600" is needed when things are not exclusively numbers correct?

Thanks

nick

 

[FormR]

Hi, you should also unquote the numbers "0","1", etc should be 0,1 else they will be returned as text and not numbers.

=IF(BI2="over_600",0,IF(BI2="450_600",1,IF(BI2="301_450",2,IF(BI2="151_300",3,IF(BI2="51_150",4,IF(BI2="0_50",5,0))))))

And an alternative.

=IFNA(MATCH(BI2,{"over_600","450_600","301_450","151_300","51_150","0_50"},0)-1,0)