A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

0 if blank



 
 
Thread Tools Display Modes
  #1  
Old January 7th, 2004, 05:21 PM
Theo
external usenet poster
 
Posts: n/a
Default 0 if blank

Is there a function available that will interpret a blank
cell as 0?
  #2  
Old January 7th, 2004, 05:31 PM
Paul
external usenet poster
 
Posts: n/a
Default 0 if blank

"Theo" wrote in message
...
Is there a function available that will interpret a blank
cell as 0?


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.


  #3  
Old January 7th, 2004, 05:45 PM
external usenet poster
 
Posts: n/a
Default 0 if blank


-----Original Message-----
"Theo" wrote in

message
...
Is there a function available that will interpret a

blank
cell as 0?


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

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
  #4  
Old January 7th, 2004, 05:50 PM
Peo Sjoblom
external usenet poster
 
Posts: n/a
Default 0 if blank

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

--

Regards,

Peo Sjoblom


wrote in message
...

-----Original Message-----
"Theo" wrote in

message
...
Is there a function available that will interpret a

blank
cell as 0?


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

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



  #5  
Old January 7th, 2004, 05:59 PM
Theo
external usenet poster
 
Posts: n/a
Default 0 if blank

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...

-----Original Message-----
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

--

Regards,

Peo Sjoblom


wrote in message
...

-----Original Message-----
"Theo" wrote in

message
...
Is there a function available that will interpret a

blank
cell as 0?

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

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



.

  #6  
Old January 7th, 2004, 06:00 PM
Paul
external usenet poster
 
Posts: n/a
Default 0 if blank

wrote in message
...

-----Original Message-----
"Theo" wrote in

message
...
Is there a function available that will interpret a

blank
cell as 0?


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

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


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.


  #7  
Old January 7th, 2004, 07:22 PM
Theo
external usenet poster
 
Posts: n/a
Default 0 if blank


-----Original Message-----
wrote in message
...

-----Original Message-----
"Theo" wrote in

message
...
Is there a function available that will interpret a

blank
cell as 0?

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

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


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

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.
  #8  
Old January 7th, 2004, 09:20 PM
Harlan Grove
external usenet poster
 
Posts: n/a
Default 0 if blank

"Theo" wrote...
. . . I know that my cell has a
blank. . . .


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.


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.

--
To top-post is human, to bottom-post and snip is sublime.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:48 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.