Excel 2013 - Why are some blank cells acting as “0” and other blank cells acting as “”?
The formula I'm using in column J is =IFERROR(IF(B1>0,H1*I1,""),""). In columns H and I, most of the entries are blank (not zero), and J calculates 0. In row 23, J changes to calculate "" while B is still > 0. If I try to multiply H22 by I23, I get an error, and when I go to the calculation, it's trying to multiply (H22)"0" x (I23)"". I have no idea why it stops referring to blank cells as zero in that row, they're all the same data type (General). I tried changing them all to Number, but it didn't change anything.
I understand that empty cells are different than zero-valued cells, but both cells are blank and one is calculating as empty and the other is calculating as zero and I don't know why.
Picture with formulas
Picture with values
microsoft-excel-2013
asked Dec 31 '18 at 17:50
FrankFrank
63
|
show 3 more comments
The formula I'm using in column J is =IFERROR(IF(B1>0,H1*I1,""),""). In columns H and I, most of the entries are blank (not zero), and J calculates 0. In row 23, J changes to calculate "" while B is still > 0. If I try to multiply H22 by I23, I get an error, and when I go to the calculation, it's trying to multiply (H22)"0" x (I23)"". I have no idea why it stops referring to blank cells as zero in that row, they're all the same data type (General). I tried changing them all to Number, but it didn't change anything.
I understand that empty cells are different than zero-valued cells, but both cells are blank and one is calculating as empty and the other is calculating as zero and I don't know why.
Picture with formulas
Picture with values
microsoft-excel-2013
asked Dec 31 '18 at 17:50
FrankFrank
63
1
Could you provide a sample or screenshots about your problem?
– Lee
Jan 1 at 9:08
Your general formula calculates based on everything being in row 1. The problem you describe has theHandIcells in different rows. If that isn't the cause, we would need to see what's actually there.
– fixer1234
Jan 1 at 10:06
Picture My issue is that in cell J22, the formula calculates to zero because it views H22 and I22 as "0". But then in J23, the formula calculates to #VALUE because it views H23 and I23 as "". There should be no difference between cells H22 and H23, but one is "0" and the other is "".
– Frank
Jan 2 at 13:40
Please add the images directly in your question, not the comments. Someone will inline it for you.
– Twisty Impersonator
Jan 2 at 14:51
I added links to the images, but I do not have enough reputation to post them as pictures.
– Frank
Jan 2 at 17:06
|
show 3 more comments
The formula I'm using in column J is =IFERROR(IF(B1>0,H1*I1,""),""). In columns H and I, most of the entries are blank (not zero), and J calculates 0. In row 23, J changes to calculate "" while B is still > 0. If I try to multiply H22 by I23, I get an error, and when I go to the calculation, it's trying to multiply (H22)"0" x (I23)"". I have no idea why it stops referring to blank cells as zero in that row, they're all the same data type (General). I tried changing them all to Number, but it didn't change anything.
I understand that empty cells are different than zero-valued cells, but both cells are blank and one is calculating as empty and the other is calculating as zero and I don't know why.
Picture with formulas
Picture with values
microsoft-excel-2013
asked Dec 31 '18 at 17:50
FrankFrank
63
The formula I'm using in column J is =IFERROR(IF(B1>0,H1*I1,""),""). In columns H and I, most of the entries are blank (not zero), and J calculates 0. In row 23, J changes to calculate "" while B is still > 0. If I try to multiply H22 by I23, I get an error, and when I go to the calculation, it's trying to multiply (H22)"0" x (I23)"". I have no idea why it stops referring to blank cells as zero in that row, they're all the same data type (General). I tried changing them all to Number, but it didn't change anything.
I understand that empty cells are different than zero-valued cells, but both cells are blank and one is calculating as empty and the other is calculating as zero and I don't know why.
Picture with formulas
Picture with values
microsoft-excel-2013
microsoft-excel-2013
asked Dec 31 '18 at 17:50
FrankFrank
63
asked Dec 31 '18 at 17:50
FrankFrank
63
edited Jan 2 at 17:05
Frank
asked Dec 31 '18 at 17:50
FrankFrank
63
asked Dec 31 '18 at 17:50
FrankFrank
63
asked Dec 31 '18 at 17:50
FrankFrank
63
63
1
Could you provide a sample or screenshots about your problem?
– Lee
Jan 1 at 9:08
Your general formula calculates based on everything being in row 1. The problem you describe has theHandIcells in different rows. If that isn't the cause, we would need to see what's actually there.
– fixer1234
Jan 1 at 10:06
Picture My issue is that in cell J22, the formula calculates to zero because it views H22 and I22 as "0". But then in J23, the formula calculates to #VALUE because it views H23 and I23 as "". There should be no difference between cells H22 and H23, but one is "0" and the other is "".
– Frank
Jan 2 at 13:40
Please add the images directly in your question, not the comments. Someone will inline it for you.
– Twisty Impersonator
Jan 2 at 14:51
I added links to the images, but I do not have enough reputation to post them as pictures.
– Frank
Jan 2 at 17:06
|
show 3 more comments
1
Could you provide a sample or screenshots about your problem?
– Lee
Jan 1 at 9:08
Your general formula calculates based on everything being in row 1. The problem you describe has theHandIcells in different rows. If that isn't the cause, we would need to see what's actually there.
– fixer1234
Jan 1 at 10:06
Picture My issue is that in cell J22, the formula calculates to zero because it views H22 and I22 as "0". But then in J23, the formula calculates to #VALUE because it views H23 and I23 as "". There should be no difference between cells H22 and H23, but one is "0" and the other is "".
– Frank
Jan 2 at 13:40
Please add the images directly in your question, not the comments. Someone will inline it for you.
– Twisty Impersonator
Jan 2 at 14:51
I added links to the images, but I do not have enough reputation to post them as pictures.
– Frank
Jan 2 at 17:06
1
1
Could you provide a sample or screenshots about your problem?
– Lee
Jan 1 at 9:08
Could you provide a sample or screenshots about your problem?
– Lee
Jan 1 at 9:08
Your general formula calculates based on everything being in row 1. The problem you describe has the
H and I cells in different rows. If that isn't the cause, we would need to see what's actually there.– fixer1234
Jan 1 at 10:06
Your general formula calculates based on everything being in row 1. The problem you describe has the
H and I cells in different rows. If that isn't the cause, we would need to see what's actually there.– fixer1234
Jan 1 at 10:06
Picture My issue is that in cell J22, the formula calculates to zero because it views H22 and I22 as "0". But then in J23, the formula calculates to #VALUE because it views H23 and I23 as "". There should be no difference between cells H22 and H23, but one is "0" and the other is "".
– Frank
Jan 2 at 13:40
Picture My issue is that in cell J22, the formula calculates to zero because it views H22 and I22 as "0". But then in J23, the formula calculates to #VALUE because it views H23 and I23 as "". There should be no difference between cells H22 and H23, but one is "0" and the other is "".
– Frank
Jan 2 at 13:40
Please add the images directly in your question, not the comments. Someone will inline it for you.
– Twisty Impersonator
Jan 2 at 14:51
Please add the images directly in your question, not the comments. Someone will inline it for you.
– Twisty Impersonator
Jan 2 at 14:51
I added links to the images, but I do not have enough reputation to post them as pictures.
– Frank
Jan 2 at 17:06
I added links to the images, but I do not have enough reputation to post them as pictures.
– Frank
Jan 2 at 17:06
|
show 3 more comments
1 Answer
1
active
oldest
votes
The value "" is text and cannot be used as a number.
Trying to use it in direct arithmetic will cause the dreaded #VALUE! error.
However, most functions will ignore such values.
And, yes, this is inconsistent.
To avoid the error you should therefore use a function.
For example, the
PRODUCT function.
When multiplying a number with an empty value, PRODUCT will ignore
the empty cell and return the other non-empty cell as its value.
If this is not what you want, use the ISNUMBER function similar to:
=IF(ISNUMBER(A1);A1*B1;0).
answered Dec 31 '18 at 19:30
harrymcharrymc
255k14266566
I understand the difference and why I can't use "" in a formula. I'm trying to determine why Excel is treating two adjacent blank cells as two different values. One is "0" and the other is "", and they're both formatted identically.
– Frank
Jan 2 at 13:43
They are most probably not identical. One might be truly empty while the other was assigned"".
– harrymc
Jan 2 at 14:48
to check out what type are a value in a cell could use =TYPE(A1) will show you the type of value in cell A1, reference support.office.com/en-us/article/…
– AtomiX84
Jan 2 at 14:52
Ok so they're definitely different types, one is a number and one is text, but why? And how do I change the type from text to a number? I changed the number format setting and it did not fix it.
– Frank
Jan 2 at 17:03
The=CODE(A1)formula will return a#VALUE!if the cell is truly empty, otherwise a number which is the ASCII character number used in=CHAR(?). What does CODE return for the two cells?
– harrymc
Jan 2 at 17:11
|
show 4 more comments
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1389341%2fexcel-2013-why-are-some-blank-cells-acting-as-0-and-other-blank-cells-acting%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The value "" is text and cannot be used as a number.
Trying to use it in direct arithmetic will cause the dreaded #VALUE! error.
However, most functions will ignore such values.
And, yes, this is inconsistent.
To avoid the error you should therefore use a function.
For example, the
PRODUCT function.
When multiplying a number with an empty value, PRODUCT will ignore
the empty cell and return the other non-empty cell as its value.
If this is not what you want, use the ISNUMBER function similar to:
=IF(ISNUMBER(A1);A1*B1;0).
answered Dec 31 '18 at 19:30
harrymcharrymc
255k14266566
I understand the difference and why I can't use "" in a formula. I'm trying to determine why Excel is treating two adjacent blank cells as two different values. One is "0" and the other is "", and they're both formatted identically.
– Frank
Jan 2 at 13:43
They are most probably not identical. One might be truly empty while the other was assigned"".
– harrymc
Jan 2 at 14:48
to check out what type are a value in a cell could use =TYPE(A1) will show you the type of value in cell A1, reference support.office.com/en-us/article/…
– AtomiX84
Jan 2 at 14:52
Ok so they're definitely different types, one is a number and one is text, but why? And how do I change the type from text to a number? I changed the number format setting and it did not fix it.
– Frank
Jan 2 at 17:03
The=CODE(A1)formula will return a#VALUE!if the cell is truly empty, otherwise a number which is the ASCII character number used in=CHAR(?). What does CODE return for the two cells?
– harrymc
Jan 2 at 17:11
|
show 4 more comments
The value "" is text and cannot be used as a number.
Trying to use it in direct arithmetic will cause the dreaded #VALUE! error.
However, most functions will ignore such values.
And, yes, this is inconsistent.
To avoid the error you should therefore use a function.
For example, the
PRODUCT function.
When multiplying a number with an empty value, PRODUCT will ignore
the empty cell and return the other non-empty cell as its value.
If this is not what you want, use the ISNUMBER function similar to:
=IF(ISNUMBER(A1);A1*B1;0).
answered Dec 31 '18 at 19:30
harrymcharrymc
255k14266566
I understand the difference and why I can't use "" in a formula. I'm trying to determine why Excel is treating two adjacent blank cells as two different values. One is "0" and the other is "", and they're both formatted identically.
– Frank
Jan 2 at 13:43
They are most probably not identical. One might be truly empty while the other was assigned"".
– harrymc
Jan 2 at 14:48
to check out what type are a value in a cell could use =TYPE(A1) will show you the type of value in cell A1, reference support.office.com/en-us/article/…
– AtomiX84
Jan 2 at 14:52
Ok so they're definitely different types, one is a number and one is text, but why? And how do I change the type from text to a number? I changed the number format setting and it did not fix it.
– Frank
Jan 2 at 17:03
The=CODE(A1)formula will return a#VALUE!if the cell is truly empty, otherwise a number which is the ASCII character number used in=CHAR(?). What does CODE return for the two cells?
– harrymc
Jan 2 at 17:11
|
show 4 more comments
The value "" is text and cannot be used as a number.
Trying to use it in direct arithmetic will cause the dreaded #VALUE! error.
However, most functions will ignore such values.
And, yes, this is inconsistent.
To avoid the error you should therefore use a function.
For example, the
PRODUCT function.
When multiplying a number with an empty value, PRODUCT will ignore
the empty cell and return the other non-empty cell as its value.
If this is not what you want, use the ISNUMBER function similar to:
=IF(ISNUMBER(A1);A1*B1;0).
answered Dec 31 '18 at 19:30
harrymcharrymc
255k14266566
The value "" is text and cannot be used as a number.
Trying to use it in direct arithmetic will cause the dreaded #VALUE! error.
However, most functions will ignore such values.
And, yes, this is inconsistent.
To avoid the error you should therefore use a function.
For example, the
PRODUCT function.
When multiplying a number with an empty value, PRODUCT will ignore
the empty cell and return the other non-empty cell as its value.
If this is not what you want, use the ISNUMBER function similar to:
=IF(ISNUMBER(A1);A1*B1;0).
answered Dec 31 '18 at 19:30
harrymcharrymc
255k14266566
edited Jan 2 at 14:46
answered Dec 31 '18 at 19:30
harrymcharrymc
255k14266566
answered Dec 31 '18 at 19:30
harrymcharrymc
255k14266566
answered Dec 31 '18 at 19:30
harrymcharrymc
255k14266566
255k14266566
I understand the difference and why I can't use "" in a formula. I'm trying to determine why Excel is treating two adjacent blank cells as two different values. One is "0" and the other is "", and they're both formatted identically.
– Frank
Jan 2 at 13:43
They are most probably not identical. One might be truly empty while the other was assigned"".
– harrymc
Jan 2 at 14:48
to check out what type are a value in a cell could use =TYPE(A1) will show you the type of value in cell A1, reference support.office.com/en-us/article/…
– AtomiX84
Jan 2 at 14:52
Ok so they're definitely different types, one is a number and one is text, but why? And how do I change the type from text to a number? I changed the number format setting and it did not fix it.
– Frank
Jan 2 at 17:03
The=CODE(A1)formula will return a#VALUE!if the cell is truly empty, otherwise a number which is the ASCII character number used in=CHAR(?). What does CODE return for the two cells?
– harrymc
Jan 2 at 17:11
|
show 4 more comments
I understand the difference and why I can't use "" in a formula. I'm trying to determine why Excel is treating two adjacent blank cells as two different values. One is "0" and the other is "", and they're both formatted identically.
– Frank
Jan 2 at 13:43
They are most probably not identical. One might be truly empty while the other was assigned"".
– harrymc
Jan 2 at 14:48
to check out what type are a value in a cell could use =TYPE(A1) will show you the type of value in cell A1, reference support.office.com/en-us/article/…
– AtomiX84
Jan 2 at 14:52
Ok so they're definitely different types, one is a number and one is text, but why? And how do I change the type from text to a number? I changed the number format setting and it did not fix it.
– Frank
Jan 2 at 17:03
The=CODE(A1)formula will return a#VALUE!if the cell is truly empty, otherwise a number which is the ASCII character number used in=CHAR(?). What does CODE return for the two cells?
– harrymc
Jan 2 at 17:11
I understand the difference and why I can't use "" in a formula. I'm trying to determine why Excel is treating two adjacent blank cells as two different values. One is "0" and the other is "", and they're both formatted identically.
– Frank
Jan 2 at 13:43
I understand the difference and why I can't use "" in a formula. I'm trying to determine why Excel is treating two adjacent blank cells as two different values. One is "0" and the other is "", and they're both formatted identically.
– Frank
Jan 2 at 13:43
They are most probably not identical. One might be truly empty while the other was assigned
"".– harrymc
Jan 2 at 14:48
They are most probably not identical. One might be truly empty while the other was assigned
"".– harrymc
Jan 2 at 14:48
to check out what type are a value in a cell could use =TYPE(A1) will show you the type of value in cell A1, reference support.office.com/en-us/article/…
– AtomiX84
Jan 2 at 14:52
to check out what type are a value in a cell could use =TYPE(A1) will show you the type of value in cell A1, reference support.office.com/en-us/article/…
– AtomiX84
Jan 2 at 14:52
Ok so they're definitely different types, one is a number and one is text, but why? And how do I change the type from text to a number? I changed the number format setting and it did not fix it.
– Frank
Jan 2 at 17:03
Ok so they're definitely different types, one is a number and one is text, but why? And how do I change the type from text to a number? I changed the number format setting and it did not fix it.
– Frank
Jan 2 at 17:03
The
=CODE(A1) formula will return a #VALUE! if the cell is truly empty, otherwise a number which is the ASCII character number used in =CHAR(?). What does CODE return for the two cells?– harrymc
Jan 2 at 17:11
The
=CODE(A1) formula will return a #VALUE! if the cell is truly empty, otherwise a number which is the ASCII character number used in =CHAR(?). What does CODE return for the two cells?– harrymc
Jan 2 at 17:11
|
show 4 more comments
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1389341%2fexcel-2013-why-are-some-blank-cells-acting-as-0-and-other-blank-cells-acting%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Could you provide a sample or screenshots about your problem?
– Lee
Jan 1 at 9:08
Your general formula calculates based on everything being in row 1. The problem you describe has the
HandIcells in different rows. If that isn't the cause, we would need to see what's actually there.– fixer1234
Jan 1 at 10:06
Picture My issue is that in cell J22, the formula calculates to zero because it views H22 and I22 as "0". But then in J23, the formula calculates to #VALUE because it views H23 and I23 as "". There should be no difference between cells H22 and H23, but one is "0" and the other is "".
– Frank
Jan 2 at 13:40
Please add the images directly in your question, not the comments. Someone will inline it for you.
– Twisty Impersonator
Jan 2 at 14:51
I added links to the images, but I do not have enough reputation to post them as pictures.
– Frank
Jan 2 at 17:06