# Disregarding blank cells when performing calculations

1. ## No replace for #Value?

Is there no way to request #Value to be replaced with an empty string?

2. ## Formula-error

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

3. ## Disregarding blank cells when performing calculations

Problem:

Columns A:B contain numbers, as well as empty cells.
How could we multiply each number in column A with the matching number in Column B without getting false results or errors?

Solution:

Using the ISNUMBER function will allow us to validate the values prior to the calculation.
Thus, when failing to multiply a pair of values, the formula will return a blank cell, rather than an error or 0.
Following is the formula:
=IF(AND(ISNUMBER(\$A\$2:\$A\$5), ISNUMBER(\$B\$2:\$B\$5)),A2*B2,\"\"\"\")

Example:

List1___List2___Result
2_______3_______6
5
8
11______4_______44

4. ## Ignoring Blank Cells when Performing Calculations

This formula is more useful when the cell has a non numeric entry, say a text entry. In which case but for this formula it would have given a result "#VALUE!"
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"

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1