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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
0 if blank
Is there a function available that will interpret a blank
cell as 0? |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|