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

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

Herewith attached is my excel sheet. 8 Replies

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

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

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

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

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

``=--MID(A2,3,2)``

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

Column f is formatted as general not text @Sergei Baklan

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

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.

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

@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.

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

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

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

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. 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.