» Avoiding Problems when Numeric Values are used as Text
CATEGORY - Excel Text Formulas
VERSION - All Microsoft Excel Versions
Problem:The following formula in column B was designed to return "5" for all the numbers in List1 (column A) that are greater than or equal to 5, and "2" for all the numbers that are less than 5.
=IF(A2>=5,"5","2")
This appears successful, however, when the following SUM formula is used to provide a total of the results in column B, an incorrect result of "0" is returned:
=SUM(B2:B8)
Solution:
The numbers "5" and "2", returned by the IF function in column B, are actually text values because they are entered within double quotes.
Hence, column B contains text values, not numbers.
As the SUM function operates on numeric values only, there are no numbers in column B to add up, and therefore, it returns 0.
To solve the problem, we must convert the text values in column B to numbers in one of the following ways:
1. Modify the IF function in column B to return numbers by removing the double quotes from "5" and "2", as follows:
=IF(A2>=5,5,2)
2. Use the SUM and VALUE functions to convert the text values in column B to numbers, using one the following Array formulas:
{=SUM(--B2:B8)}
{=SUM(VALUE(B2:B8))}
To apply Array formula:
Select the cell, press
Book Store:
No comments have been submitted.
