» Ignoring Blank Cells when Performing Calculations
CATEGORY - Excel Array Formulas
VERSION - All Microsoft Excel Versions
Columns A & B contain numbers as well as empty cells.
We want to be able to multiply each number in column A with the matching number in Column B without getting false results or errors.
Solution:
Use IF, AND and ISNUMBER functions in the following formula:
=IF(AND(ISNUMBER(A2), ISNUMBER(B2)),A2*B2," ")
Example:
List1___List2___Result
2_______3_______6
5
8
11______4_______44
Book Store:
Recommended Books:
- The McGraw-Hill Guide to Writing a High-Impact Business Plan: A Proven Blueprint for First-Time Entrepreneurs
- The Financial Numbers Game: Detecting Creative Accounting Practices
- Financial Statement Analysis: A Practitioner's Guide, 3rd Edition
- Microsoft PowerPoint Version 2002 Step by Step
- Financial Shenanigans : How to Detect Accounting Gimmicks & Fraud in Financial Reports
- Who Moved My Cheese? An Amazing Way to Deal with Change in Your Work and in Your Life
Formula-error
Harager
I found out that in above written formula there was made a mistake, it should be :
IF(AND(ISNUMBER(A2);ISNUMBER(B2)); A2*B2; " ")
where between ISNUMBER(A2) and ISNUMBER(B2) there should be ';' and not ','
With friendly greetings,
Harager
No replace for #Value?
Moshe
Is there no way to request #Value to be replaced with an empty string?
Which would have meant one could not sum the value up.
If the cell is blank, without the formula it would give a result "0", with the formula it returns with a blank text entry.
This forumla is more appropriate termed as "ignoring text cells when performing calculations"