» 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:
- How to Use Financial Statements: A Guide to Understanding the Numbers
- What the IRS Doesn't Want You to Know: A Cpa Reveals the Tricks of the Trade
- Microsoft Word Version 2002 Step By Step (With CD-ROM)
- The Accounting Game : Basic Accounting Fresh from the Lemonade Stand
- Positioning: The Battle for Your Mind
- Absolute Beginner's Guide to Microsoft Excel 2002
No comments have been submitted.
