» 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:
- The McGraw-Hill Guide to Writing a High-Impact Business Plan: A Proven Blueprint for First-Time Entrepreneurs
- Word 2002: The Complete Reference
- Financial Modeling - 2nd Edition
- Definitive Guide to Excel VBA
- Managing by the Numbers: A Commonsense Guide to Understanding and Using Your Company's Financials: An Essential Resource for Growing Businesses
- Successful Business Planning in 30 Days: A Step-By-Step Guide for Writing a Business Plan and Starting Your Own Business
No comments have been submitted.
