Mixing
Formula Wizard Result Doesn't Match Displayed Cell Value
0
I have a simple formula that is displaying correctly in every cell above and below one problem cell. It is showing as blank (and if you try to do a sum, it is truly blank), but the function argument displays the result as 3,313.
- Auto-calculations are on.
- If I add this cell to a cell that's equal to 10, the result is 10
- The formula is:
=IF(SUM(X25:X106<1),"",X25)
I suspect the issue is not with the formula, as it works everywhere else.
excel excel-formula formula
asked Nov 20 '18 at 20:22
Aria DewesAria Dewes
133
add a comment |
0
I have a simple formula that is displaying correctly in every cell above and below one problem cell. It is showing as blank (and if you try to do a sum, it is truly blank), but the function argument displays the result as 3,313.
- Auto-calculations are on.
- If I add this cell to a cell that's equal to 10, the result is 10
- The formula is:
=IF(SUM(X25:X106<1),"",X25)
I suspect the issue is not with the formula, as it works everywhere else.
excel excel-formula formula
asked Nov 20 '18 at 20:22
Aria DewesAria Dewes
133
add a comment |
0
0
0
1
I have a simple formula that is displaying correctly in every cell above and below one problem cell. It is showing as blank (and if you try to do a sum, it is truly blank), but the function argument displays the result as 3,313.
- Auto-calculations are on.
- If I add this cell to a cell that's equal to 10, the result is 10
- The formula is:
=IF(SUM(X25:X106<1),"",X25)
I suspect the issue is not with the formula, as it works everywhere else.
excel excel-formula formula
asked Nov 20 '18 at 20:22
Aria DewesAria Dewes
133
I have a simple formula that is displaying correctly in every cell above and below one problem cell. It is showing as blank (and if you try to do a sum, it is truly blank), but the function argument displays the result as 3,313.
- Auto-calculations are on.
- If I add this cell to a cell that's equal to 10, the result is 10
- The formula is:
=IF(SUM(X25:X106<1),"",X25)
I suspect the issue is not with the formula, as it works everywhere else.
excel excel-formula formula
excel excel-formula formula
asked Nov 20 '18 at 20:22
Aria DewesAria Dewes
133
asked Nov 20 '18 at 20:22
Aria DewesAria Dewes
133
edited Nov 20 '18 at 20:50
Aria Dewes
asked Nov 20 '18 at 20:22
Aria DewesAria Dewes
133
asked Nov 20 '18 at 20:22
Aria DewesAria Dewes
133
asked Nov 20 '18 at 20:22
Aria DewesAria Dewes
133
133
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
You have the ) wrong:
=IF(SUM(X25:X106)<1,"",X25)
answered Nov 20 '18 at 20:24
Scott CranerScott Craner
90.5k82550
Unfortunately isolating the sum is not working, and this formula works in other cells without issue. Strangely enough, if I flip the greater than sign, it works. The formula wizard shows the 3,313 total for both greater than and less than.
– Aria Dewes
Nov 20 '18 at 20:35
Update: Thank you! After isolating the sum in exactly the same manner, it worked (but only after the 10th time or so). If anyone understands why my first formula wasn't working in one instance out of 80+, I'm very interested! I'm going to be losing sleep over this!
– Aria Dewes
Nov 20 '18 at 20:48
@AriaDewes Well, the original formula was wrong. The result ofX25:X106<1is an array with TRUE/FALSE values, and the sum of that is always zero, unless you put--before, which converts the TRUE to 1, so you would actually get the number of TRUE values. But I assume this is not at all what you wanted to do. The fact that it worked (or gave a predicted result) for other cells does not mean that the formula was correct !
– Peter K.
Nov 20 '18 at 22:14
Thank you! I didn't know the sum function could be an array. I'll write my formulas more neatly in the future- your explanation really helped!
– Aria Dewes
Nov 21 '18 at 19:28
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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
});
}
});
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53400950%2fformula-wizard-result-doesnt-match-displayed-cell-value%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
1
You have the ) wrong:
=IF(SUM(X25:X106)<1,"",X25)
answered Nov 20 '18 at 20:24
Scott CranerScott Craner
90.5k82550
Unfortunately isolating the sum is not working, and this formula works in other cells without issue. Strangely enough, if I flip the greater than sign, it works. The formula wizard shows the 3,313 total for both greater than and less than.
– Aria Dewes
Nov 20 '18 at 20:35
Update: Thank you! After isolating the sum in exactly the same manner, it worked (but only after the 10th time or so). If anyone understands why my first formula wasn't working in one instance out of 80+, I'm very interested! I'm going to be losing sleep over this!
– Aria Dewes
Nov 20 '18 at 20:48
@AriaDewes Well, the original formula was wrong. The result ofX25:X106<1is an array with TRUE/FALSE values, and the sum of that is always zero, unless you put--before, which converts the TRUE to 1, so you would actually get the number of TRUE values. But I assume this is not at all what you wanted to do. The fact that it worked (or gave a predicted result) for other cells does not mean that the formula was correct !
– Peter K.
Nov 20 '18 at 22:14
Thank you! I didn't know the sum function could be an array. I'll write my formulas more neatly in the future- your explanation really helped!
– Aria Dewes
Nov 21 '18 at 19:28
add a comment |
1
You have the ) wrong:
=IF(SUM(X25:X106)<1,"",X25)
answered Nov 20 '18 at 20:24
Scott CranerScott Craner
90.5k82550
Unfortunately isolating the sum is not working, and this formula works in other cells without issue. Strangely enough, if I flip the greater than sign, it works. The formula wizard shows the 3,313 total for both greater than and less than.
– Aria Dewes
Nov 20 '18 at 20:35
Update: Thank you! After isolating the sum in exactly the same manner, it worked (but only after the 10th time or so). If anyone understands why my first formula wasn't working in one instance out of 80+, I'm very interested! I'm going to be losing sleep over this!
– Aria Dewes
Nov 20 '18 at 20:48
@AriaDewes Well, the original formula was wrong. The result ofX25:X106<1is an array with TRUE/FALSE values, and the sum of that is always zero, unless you put--before, which converts the TRUE to 1, so you would actually get the number of TRUE values. But I assume this is not at all what you wanted to do. The fact that it worked (or gave a predicted result) for other cells does not mean that the formula was correct !
– Peter K.
Nov 20 '18 at 22:14
Thank you! I didn't know the sum function could be an array. I'll write my formulas more neatly in the future- your explanation really helped!
– Aria Dewes
Nov 21 '18 at 19:28
add a comment |
1
1
1
You have the ) wrong:
=IF(SUM(X25:X106)<1,"",X25)
answered Nov 20 '18 at 20:24
Scott CranerScott Craner
90.5k82550
You have the ) wrong:
=IF(SUM(X25:X106)<1,"",X25)
answered Nov 20 '18 at 20:24
Scott CranerScott Craner
90.5k82550
answered Nov 20 '18 at 20:24
Scott CranerScott Craner
90.5k82550
answered Nov 20 '18 at 20:24
Scott CranerScott Craner
90.5k82550
answered Nov 20 '18 at 20:24
Scott CranerScott Craner
90.5k82550
90.5k82550
Unfortunately isolating the sum is not working, and this formula works in other cells without issue. Strangely enough, if I flip the greater than sign, it works. The formula wizard shows the 3,313 total for both greater than and less than.
– Aria Dewes
Nov 20 '18 at 20:35
Update: Thank you! After isolating the sum in exactly the same manner, it worked (but only after the 10th time or so). If anyone understands why my first formula wasn't working in one instance out of 80+, I'm very interested! I'm going to be losing sleep over this!
– Aria Dewes
Nov 20 '18 at 20:48
@AriaDewes Well, the original formula was wrong. The result ofX25:X106<1is an array with TRUE/FALSE values, and the sum of that is always zero, unless you put--before, which converts the TRUE to 1, so you would actually get the number of TRUE values. But I assume this is not at all what you wanted to do. The fact that it worked (or gave a predicted result) for other cells does not mean that the formula was correct !
– Peter K.
Nov 20 '18 at 22:14
Thank you! I didn't know the sum function could be an array. I'll write my formulas more neatly in the future- your explanation really helped!
– Aria Dewes
Nov 21 '18 at 19:28
add a comment |
Unfortunately isolating the sum is not working, and this formula works in other cells without issue. Strangely enough, if I flip the greater than sign, it works. The formula wizard shows the 3,313 total for both greater than and less than.
– Aria Dewes
Nov 20 '18 at 20:35
Update: Thank you! After isolating the sum in exactly the same manner, it worked (but only after the 10th time or so). If anyone understands why my first formula wasn't working in one instance out of 80+, I'm very interested! I'm going to be losing sleep over this!
– Aria Dewes
Nov 20 '18 at 20:48
@AriaDewes Well, the original formula was wrong. The result ofX25:X106<1is an array with TRUE/FALSE values, and the sum of that is always zero, unless you put--before, which converts the TRUE to 1, so you would actually get the number of TRUE values. But I assume this is not at all what you wanted to do. The fact that it worked (or gave a predicted result) for other cells does not mean that the formula was correct !
– Peter K.
Nov 20 '18 at 22:14
Thank you! I didn't know the sum function could be an array. I'll write my formulas more neatly in the future- your explanation really helped!
– Aria Dewes
Nov 21 '18 at 19:28
Unfortunately isolating the sum is not working, and this formula works in other cells without issue. Strangely enough, if I flip the greater than sign, it works. The formula wizard shows the 3,313 total for both greater than and less than.
– Aria Dewes
Nov 20 '18 at 20:35
Unfortunately isolating the sum is not working, and this formula works in other cells without issue. Strangely enough, if I flip the greater than sign, it works. The formula wizard shows the 3,313 total for both greater than and less than.
– Aria Dewes
Nov 20 '18 at 20:35
Update: Thank you! After isolating the sum in exactly the same manner, it worked (but only after the 10th time or so). If anyone understands why my first formula wasn't working in one instance out of 80+, I'm very interested! I'm going to be losing sleep over this!
– Aria Dewes
Nov 20 '18 at 20:48
Update: Thank you! After isolating the sum in exactly the same manner, it worked (but only after the 10th time or so). If anyone understands why my first formula wasn't working in one instance out of 80+, I'm very interested! I'm going to be losing sleep over this!
– Aria Dewes
Nov 20 '18 at 20:48
@AriaDewes Well, the original formula was wrong. The result of
X25:X106<1 is an array with TRUE/FALSE values, and the sum of that is always zero, unless you put -- before, which converts the TRUE to 1, so you would actually get the number of TRUE values. But I assume this is not at all what you wanted to do. The fact that it worked (or gave a predicted result) for other cells does not mean that the formula was correct !– Peter K.
Nov 20 '18 at 22:14
@AriaDewes Well, the original formula was wrong. The result of
X25:X106<1 is an array with TRUE/FALSE values, and the sum of that is always zero, unless you put -- before, which converts the TRUE to 1, so you would actually get the number of TRUE values. But I assume this is not at all what you wanted to do. The fact that it worked (or gave a predicted result) for other cells does not mean that the formula was correct !– Peter K.
Nov 20 '18 at 22:14
Thank you! I didn't know the sum function could be an array. I'll write my formulas more neatly in the future- your explanation really helped!
– Aria Dewes
Nov 21 '18 at 19:28
Thank you! I didn't know the sum function could be an array. I'll write my formulas more neatly in the future- your explanation really helped!
– Aria Dewes
Nov 21 '18 at 19:28
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- 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.
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53400950%2fformula-wizard-result-doesnt-match-displayed-cell-value%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
