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 and simultaneously press .
Screenshot // Avoiding Problems when Numeric Values are used as Text
Avoiding Problems when Numeric Values are used as Text
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
