How to find if a Numbers cell is empty?

Question

Level 1

52 points

How to find if a Numbers cell is empty?

I am getting this error (Blue trianlge in the result cell)

here is the formula

=IF(AND(D2="P1/Us",P2>""),P2,"")

also tried

=IF(AND(D2="P1/Us",P2>0),P2,"")

And get a "Cant compare date with a number,because data types are different"

the data in column P is time in the format HH:MM.

I just want to check if there is an entry in a cell...I am not interested in what type it is

A null string or a 0 does not work.

I do not want to maniluplate the time at all, all I want to do is look in that cell (P2) and see if there is an entry. If there is, then copy it to the cell where the formula is, else leave it blank.

I have seen the ISBLANK fucntion and I want the opposite...so use the NOT fucnction as well???
I do not want to give in, but it is getting that way
I'll almost be as quick going through the 8000 lines of this data and doing it manually at this rate

And as usual I am incapable of using the Help function or User Guide to find a relevant answer. That is the problem with help functions,,,if you know the term to search for you hare half way towards to knowing how to use it

Mac Pro 2 x Quad Core 3.0Ghz 10Gb RAM 4 x 1Tb HD's, Mac OS X (10.6.1), FreeNAS servers and Other Windoze and Linux machines

Posted on Nov 13, 2010 4:41 AM

Reply

Answer 1

KOENIG Yvan

Level 8

41,819 points

Nov 14, 2010 9:10 AM in response to Neil Paisnel

Question asked and responded several times in this forum.
The search tool is available for every users, isn't it 😉
To check if a cell "is empty", we must know what means "is empty" for you.

Assuming that it means :
the cell is really empty =ISBLANK(the_cell) returns TRUE
or
the cell contains a string whose length is zero

the well known soluce is the formula :

=LEN(the_cell)=0

So you may use :

=IF(AND(D2="P1/Us",LEN(P2)>0),P2,"")

You're right that this is not described in the User Guide and it's normal.

For Numbers designers, a cell is empty when it contains absolutely nothing.
A cell containing a string whose length is zero is not really empty.

The true problem is that there is no way to define the value of a cell as (really empty) thru a formula.
So, we are forced to use an other definition of "empty cell".

Yvan KOENIG (VALLAURIS, France) samedi 13 novembre 2010 17:29:42

Reply

Answer 2

Neil Paisnel Author

Level 1

52 points

Nov 14, 2010 9:10 AM in response to Badunit

Thanks to the both of you for that.
Again it is a Syntax thing, I had looked at the NOT command, in conjunction but could not get it to work.

I seem to have solved my constant Syntax errors though, ; using the function browser and the Insert function button,.

Thanks again

Reply

Answer 3

Badunit

Level 7

30,318 points

Nov 14, 2010 9:10 AM in response to Neil Paisnel

Replacing P2>0 with NOT(ISBLANK(P2)) should do it.

Reply