Treating empty cells as missing data in f…
apple Verified ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
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).
Any ideas?
9 replies
Sign in or join Beachball Help to reply.
- 0
Badunit ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
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?
- 0
Barry ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
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 - 0
quercuslobata ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Sorry for the confusion... Say that I have a huge dataset with a number of columns of numeric (not string) data and I want to perform a mathematical operation, how do I get the correct answer if there is missing data for some cells (empty cells that should not be zero)?
The dot is just an example of how this is handled in Excel ( i.e. avg 3 3 = 2, but avg 3 3 . =3). I have no particular attachment to the dot, I just want my formula to work correctly.
Is that any clearer? Thanks for the help.
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
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
- 0
quercuslobata ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I am trying to think different, but I am new at this and I still need my formula to work right. I can see that the built in function avg was a bad example because unlike excel Numbers does this right.
A more clear example (I hope) of what I'm trying to do is this:
Say I have a number of observations of sizes
178
182194
196I know the minimum possible size (say 176) and I want to compute (observed size-minimum size)/2. The blank cell results in a negative number, but what I want is a blank cell.
Any thoughts?
Thanks again for being patient with a newbie.
- 0
Jerrold Green1 ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
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
- 0
quercuslobata ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I get it now, thanks!
The first option seems like it would be tedious in a large table, but the second option seems like it will work fine. I've used a very simple IF formula before but didn't know that it could be used to do an if then else type operation.
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
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
- 0
quercuslobata ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Thanks for the help on both threads.
Empty for me means nothing, so I think that I'm ok.
