Treating empty cells as missing data in formulae in Numbers '09

Hi,
Welcome to Apple Discussions and the Numbers '09 forum.

You can set up your formula to insert an empty string as the result by enclosing the formula in an IF() statement.

=IF(ISBLANK(B),"",B*4)

AVERAGE() and some other functions will ignore the empty string (and other text values), but these will cause an error message when handled by arithmetic operators.

Regards,
Barry

I will ask what I already asked in an other thread.

What means "blank cell" for you ?

(1) For Numbers, a blank cell contains nothing.
(2) For many users, a blank cell contains nothing or contains a string whose length is zero.

In case (1), using the ISBLANK() function is perfect
In case (2), itsn't. replace it by
=IF(COUNT(theCell)=0,"",theCalculation)

Yvan KOENIG (VALLAURIS, France) vendredi 11 juin 2010 17:16:41

When we don't want to display the result of a calculation on a certain condition, we can test for that condition and substitute a different output. In your case, you may have blank input cell and you don't want to display the negative result that would otherwise occur. There are at least two general approaches that you could use. The easiest would be to go ahead and make the calculation and then format all negative results to a font that wouldn't be visible, using Conditional Format. A slightly more complicated way, as Barry described above, would be to test for the blank cell condition and substitute a null string. Here's a graphic of the null string case.



When you have learned a few basic tricks this will all seem easy to you.

Jerry

I do not understand what you are trying to do. Are you trying to use Find and Replace to put a dot in empty cells or is this a question about a formula, or is this two separate questions? Which formula in particular is giving you a problem with regards to treating empty cells as zeros?

quercuslobata wrote:
I'm working with numerical data where many cells are empty (missing data). My problem is that when I use formula to make calculations, Numbers treats empty cells as zeros, and won't let me search and replace an empty cell with a placeholder (like . in excel).

Why would Numbers behave like Excel ?

Remember : *"Think different !"*

Yvan KOENIG (VALLAURIS, France) vendredi 11 juin 2010 11:02:27