'Else part' of if() is executed even if the condition mentioned is true.

%3CLINGO-SUB%20id%3D%22lingo-sub-2903857%22%20slang%3D%22en-US%22%3E'Else%20part'%20of%20if()%20is%20executed%20even%20if%20the%20condition%20mentioned%20is%20true.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2903857%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20i'm%20using%20if%20function%20to%20get%20value%20of%20age%20column.%3C%2FP%3E%3CP%3Eformula%20used%20is%3A%20if%20(f2%26lt%3B100%2C100-f2%2B14%2Cf2-14)%3C%2FP%3E%3CP%3EI%20am%20always%20getting%20answer%20from%20f2%20-14%20but%20the%20answer%20should%20come%20from%20100-f2%2B14%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3CP%3EHerewith%20attached%20is%20my%20excel%20sheet.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Screenshot%20(426).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321851iA5F12C3A37E1414C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(426).png%22%20alt%3D%22Screenshot%20(426).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2903857%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2903877%22%20slang%3D%22en-US%22%3ERe%3A%20'Else%20part'%20of%20if()%20is%20executed%20even%20if%20the%20condition%20mentioned%20is%20true.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2903877%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1200733%22%20target%3D%22_blank%22%3E%40Saurav_Kaura%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20F%20is%20formatted%20as%20text.%20Enter%20this%20formula%20in%20F2%20and%20copy%20down%3A%3C%2FP%3E%3CP%3E%3DNUMBERVALUE(MID(A2%2C3%2C2))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2903889%22%20slang%3D%22en-US%22%3ERe%3A%20'Else%20part'%20of%20if()%20is%20executed%20even%20if%20the%20condition%20mentioned%20is%20true.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2903889%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1200733%22%20target%3D%22_blank%22%3E%40Saurav_Kaura%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20column%20F%20you%20have%20texts%2C%20not%20numbers.%20Better%20to%20change%20on%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D--MID(A2%2C3%2C2)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2903956%22%20slang%3D%22en-US%22%3ERe%3A%20'Else%20part'%20of%20if()%20is%20executed%20even%20if%20the%20condition%20mentioned%20is%20true.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2903956%22%20slang%3D%22en-US%22%3E%3CP%3EColumn%26nbsp%3Bf%20is%20formatted%20as%20general%20not%20text%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2904868%22%20slang%3D%22en-US%22%3ERe%3A%20'Else%20part'%20of%20if()%20is%20executed%20even%20if%20the%20condition%20mentioned%20is%20true.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2904868%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1200733%22%20target%3D%22_blank%22%3E%40Saurav_Kaura%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1635664321491.png%22%20style%3D%22width%3A%20679px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321905i4A5A3152EE36205B%2Fimage-dimensions%2F679x428%3Fv%3Dv2%22%20width%3D%22679%22%20height%3D%22428%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1635664321491.png%22%20alt%3D%22Yea_So_0-1635664321491.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Here i'm using if function to get value of age column.

formula used is: if (f2<100,100-f2+14,f2-14)

I am always getting answer from f2 -14 but the answer should come from 100-f2+14

Please help.

Herewith attached is my excel sheet.Screenshot (426).png

 

8 Replies

@Saurav_Kaura 

Column F is formatted as text. Enter this formula in F2 and copy down:

=NUMBERVALUE(MID(A2,3,2))

@Saurav_Kaura 

In column F you have texts, not numbers. Better to change on

=--MID(A2,3,2)

Column f is formatted as general not text @Sergei Baklan 

@Saurav_Kaura 

It could be formatted as General, but MID() returns texts, thus values are considered as texts. You may check by ISTEXT() on cell from any empty one.

@Saurav_Kaura Not sure I follow what you are trying to achieve, but is seems you calculate the age of cars counting from the year 2014. Correct?

 

If so, use the formula provided by @Sergei Baklan in F2 and copy down and change the formula in G2 to 

=IF(F2 > 14,100-F2+14,14-F2)

and copy down, the ages will be as you would expect them to be. 

 

I have a doubt that how text format can be shown as general format in excel. Excel should show text format instead of general.

@Saurav_Kaura 

Format and type of the value are different things. If you apply General format to the cell which has Text value, you don't convert the value itself from one type to another, you need to reenter it.

 

Same if you return value by any formula. Returned value will be shown in the format already applied to the cell, formula result wont change any property of the cell, includes applied format.

 

If cell is under General format returned value will be shown as text or number depends on what formula returns. Simplest way to check - by default text is aligned to the left, and number to the right.

 

image.png

Here MID() from text always returns text and it is shown as text. If text represents number it may be  converted to number within arithmetic operation, result will be number. Thus you may multiply such text on 1; or add zero; or apply two times negation (aka "double dash") to convert such text to number.