Excel if else formula fails due to wrong syntex-CodePudding

I have different class name in excel column in character form. I want to convert it to numerical form and add in column C

If B2==N, make it 1
If B2==O, make it 0
If B2==A, make it 2
If B2==~, make it 9

I have written the following excel formula. I have added my formula and error message in image. Can you correct me where I am making mistake

=IF(B2='N',1,if(b2='O',0,if(b2='A',2,if(b2='~',9))))

CodePudding user response：

In Excel, double quotes is used to enclose character values not single quote. Hence, your formula will become

=IF(B2="N",1,IF(B2="O",0,IF(B2="A",2,IF(B2="~",9,""))))

Additionally, I have added an else clause with blanks in last IF.

Also, if you are on newer versions of Excel, then you can also use SWITCH which is easier and shorter

=SWITCH(B2,"N",1,"O",0,"A",2,"~",9,"")

CodePudding user response：

Within Excel formulas, double quotes are used to denote the start and end of a text string. Numeric should not be within double quotes.

That said, we can use a formula like IF function but we can also avoid using double quotes by creating a table of reference, refer the image below, for alternative solutions - 7 APPROACHES

• Formula used in cell C2

=VLOOKUP($B2,$I$2:$J$5,2,1)

Without Table Of Reference the above formula can be written as

=VLOOKUP($B2,{"~",9;"A",2;"N",1;"O",0},2,1)

• Formula used in cell D2

=LOOKUP($B2,$I$2:$J$5)

Without Table Of Reference the above formula can be written as

=LOOKUP($B2,{"~",9;"A",2;"N",1;"O",0})

• Formula used in cell E2 --> "Correction To Your Own Formula"

=IF(B2="N",1,IF(B2="O",0,IF(B2="A",2,IF(B2="~",9))))

• Formula used in cell F2

=IFS(B2="~",9,B2="A",2,B2="N",1,B2="O",0)

• Formula used in cell G2

=SWITCH($B2,"~",9,"A",2,"N",1,"O",0)

Note the formulas used in cell F2 & G2 you need to have either Excel 2019, Excel 2021 Or O365 hence its better to use a Table Of Reference to avoid using Double Quotes.