» Avoiding Problems when Numeric Values are used as Text
CATEGORY - Excel Text Formulas
VERSION - All Microsoft Excel Versions
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:
Recommended Books:
- Getting to Yes: Negotiating Agreement Without Giving In
- H&R Block's Just Plain Smart(tm) Tax Planning Advisor: A year-round approach to lowering your taxes this year, next year and beyond
- Financial Reporting and Analysis (2nd Edition)
- Special Edition Using Microsoft Excel 2002
- Investing in Real Estate, Fourth Edition
- Understanding Financial Statements
No comments have been submitted.
