CODEWITHSUNDEEP
excelexcel-formula
purpleblau
purpleblau

Reputation: 589

If function returns text instead of number in Excel?

I have a very simple formula, but it gives out unwanted result. 

=IF(A1>=0,A1,0)

The above formula one gives 0 as TEXT if the if-statement is false for some reason. I did IsNumber() and it returns False. How can this be?

=IF(A1>0,0,A1)

This one gives 0 as a real number in a cell if the if-statement is true referencing the same exact cell A1.

A1 =IF(ISERROR(INDEX('$G$2:$G$80,MATCH($BQ$6,'$B$2:$B$80,0))), "0.000",INDEX('$G$2:$G$80,MATCH($BQ$6,'$B$2:$B$80,0)))

Any help is appreciated.

Upvotes: 1

Views: 4023

Answers (1)

Plutian
Plutian

Reputation: 2309

Your issue is not with cell A1 being formatted as text, since you have a formula in there calculating the result. The issue is with the formula in cell A1.

=IF(ISERROR(INDEX($G$2:$G$80,MATCH($BQ$6,$B$2:$B$80,0))), "0.000",INDEX($G$2:$G$80,MATCH($BQ$6,$B$2:$B$80,0)))

(This is after cleaning the remnant ' characters, probably from where you removed references to another workbook)

In your formula, the TRUE return is "0.000", since this is in quotes, this is interpreted as text not as a number. If you were to remove the quotes, the formula will auto-correct to 0 only, returning the actual numeric value 0.

If you want this to be formatted as 0.000, don't return "0.000" but return 0 and format your cell with a numeric format with 3 decimal places.

Upvotes: 1

Related Questions