0 if blank

  • Thread starter Thread starter Theo
  • Start date Start date
Theo said:
Is there a function available that will interpret a blank
cell as 0?
Click to expand...

In most cases this happens by default.
For example, if A1, A2 and A3 contain the numbers 1, 2 and 3, whilst A4:A10
are blank, then
=SUM(A1:A10)
will return 6.

Post again if you have a specific requirement to which this doen't apply.
 
-----Original Message-----


In most cases this happens by default.
For example, if A1, A2 and A3 contain the numbers 1, 2 and 3, whilst A4:A10
are blank, then
=SUM(A1:A10)
will return 6.

Post again if you have a specific requirement to which this doen't apply.


.
Suppose I have 3 cells
Click to expand...
Normally the values are filled
10
10
10

I then have a 4th cell that add them together not using
the sum i.e. +c1+c2+c3 the total then would be 30

if the values are
10

10

using the same formula I would then get a #Value error
since the cell is blank. I want Excel to interpret the
blank cell as 0.

Thanks
 
If it returns an error it is not blank, don't confuse blank with a space
so don't enter a space in cells unless you are typing text
 
No space was entered - I just left it as is. It seems
that using the sum function works the way I want but if
you merely add two cells together I get the error. Seems
silly to use a sum function for two cells...
 
Normally the values are filled
10
10
10

I then have a 4th cell that add them together not using
the sum i.e. +c1+c2+c3 the total then would be 30

if the values are
10

10

using the same formula I would then get a #Value error
since the cell is blank. I want Excel to interpret the
blank cell as 0.

Thanks
Click to expand...

No. If the cell is truly blank, you will get the correct result (i.e. 20).
If you get the #VALUE! error it means that the cell is not blank; it may
contain a space character or something, which is making it appear to the
formula as text.

You can test to see if the cell is truly blank using the formula
=ISBLANK(C2)
which will return TRUE if it is blank.
 
-----Original Message-----


No. If the cell is truly blank, you will get the correct result (i.e. 20).
If you get the #VALUE! error it means that the cell is not blank; it may
contain a space character or something, which is making it appear to the
formula as text.

You can test to see if the cell is truly blank using the formula
=ISBLANK(C2)
which will return TRUE if it is blank.


.
Thanks for your answer Paul. I know that my cell has a
Click to expand...
blank. My original question was to see if excel can
interpret a blank as a 0. It will do so for a sum
function but not for a cell add. A blank is not the same
a null value. A null value will be interpreted as a 0
for both a sum and a cell add. You are correct that I
can test for a blank cell.
 
. . . I know that my cell has a
blank. . . .
Click to expand...

So you've checked it using =ISBLANK(C2) which returned TRUE? Or do you mean only
that =LEN(TRIM(C2)) returns 0?
. . . My original question was to see if excel can
interpret a blank as a 0. It will do so for a sum
function but not for a cell add. A blank is not the same
a null value. A null value will be interpreted as a 0
for both a sum and a cell add. You are correct that I
can test for a blank cell.
Click to expand...

Now you're using your own peculiar terminology rather than standard Excel
terminology. First, 'null' is ambiguous - if you don't mean the error value
#NULL!, what do you mean *PRECISELY*? A cell containing no formula? As for
'blank', why would anyone believe it meant anything other than a cell for which
ISBLANK returns TRUE? If you want 'null' to mean a cell for which ISBLANK(cell)
returns TRUE and 'blank' to mean a cell for which TRIM(cell) returns "", then
you need to state that explicitly at the outset of your post so that others
could have some hope of understanding what the heck you mean.

If you're misusing the term 'blank' to mean a cell for which TRIM(cell) returns
"", then =1+(cell&"0")+2 returns 3 if Transition Formula Evaluation is disabled,
and =1+VALUE(cell&"0")+2 returns 3 all the time. TRIM isn't needed in eitehr.
 
Back
Top