Avoid having 0 returned by INDEX() displayed
I have a function returning some values from a different table:
=IFERROR(INDEX(Table2[Kommentar];[@[Radnr.]]);"")
This works great for most cases, but if there's no information entered into the column in the other table, I get a 0 when I would have preferred the cell to display the same value as the other table, i.e. stay blank.
Is there any good way to do this?
Current results
Preferred results
microsoft-excel
asked Jan 10 at 8:30
eirikdaudeeirikdaude
6122620
add a comment |
I have a function returning some values from a different table:
=IFERROR(INDEX(Table2[Kommentar];[@[Radnr.]]);"")
This works great for most cases, but if there's no information entered into the column in the other table, I get a 0 when I would have preferred the cell to display the same value as the other table, i.e. stay blank.
Is there any good way to do this?
Current results
Preferred results
microsoft-excel
asked Jan 10 at 8:30
eirikdaudeeirikdaude
6122620
add a comment |
I have a function returning some values from a different table:
=IFERROR(INDEX(Table2[Kommentar];[@[Radnr.]]);"")
This works great for most cases, but if there's no information entered into the column in the other table, I get a 0 when I would have preferred the cell to display the same value as the other table, i.e. stay blank.
Is there any good way to do this?
Current results
Preferred results
microsoft-excel
asked Jan 10 at 8:30
eirikdaudeeirikdaude
6122620
I have a function returning some values from a different table:
=IFERROR(INDEX(Table2[Kommentar];[@[Radnr.]]);"")
This works great for most cases, but if there's no information entered into the column in the other table, I get a 0 when I would have preferred the cell to display the same value as the other table, i.e. stay blank.
Is there any good way to do this?
Current results
Preferred results
microsoft-excel
microsoft-excel
asked Jan 10 at 8:30
eirikdaudeeirikdaude
6122620
asked Jan 10 at 8:30
eirikdaudeeirikdaude
6122620
edited Jan 10 at 8:53
eirikdaude
asked Jan 10 at 8:30
eirikdaudeeirikdaude
6122620
asked Jan 10 at 8:30
eirikdaudeeirikdaude
6122620
asked Jan 10 at 8:30
eirikdaudeeirikdaude
6122620
6122620
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Whenever I have this, I either use conditional formatting to hide the 0s or use an IF statement:
=iferror(if(INDEX(Table2[Kommentar];[@[Radnr.]])="","",INDEX(Table2[Kommentar];[@[Radnr.]]),"")
It returns a zero in an instance of the indexed cell being blank, so simply say if the INDEX returns blank, then assume blank, else perform the INDEX as initially required.
You can also hide 0s under
file -> options -> advanced settings -> display options for this worksheet -> show a zero in cells that have a zero value
answered Jan 10 at 8:44
PeterHPeterH
3,51732548
Yeah, this was pretty much what I was planning on doing, I was hoping there was a way without having to repeat the formula though :-) And I wasn't aware of the third option, that may come in handy in the future, even if it isn't a perfect fit today.
– eirikdaude
Jan 11 at 7:59
add a comment |
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%2f1392649%2favoid-having-0-returned-by-index-displayed%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
Whenever I have this, I either use conditional formatting to hide the 0s or use an IF statement:
=iferror(if(INDEX(Table2[Kommentar];[@[Radnr.]])="","",INDEX(Table2[Kommentar];[@[Radnr.]]),"")
It returns a zero in an instance of the indexed cell being blank, so simply say if the INDEX returns blank, then assume blank, else perform the INDEX as initially required.
You can also hide 0s under
file -> options -> advanced settings -> display options for this worksheet -> show a zero in cells that have a zero value
answered Jan 10 at 8:44
PeterHPeterH
3,51732548
Yeah, this was pretty much what I was planning on doing, I was hoping there was a way without having to repeat the formula though :-) And I wasn't aware of the third option, that may come in handy in the future, even if it isn't a perfect fit today.
– eirikdaude
Jan 11 at 7:59
add a comment |
Whenever I have this, I either use conditional formatting to hide the 0s or use an IF statement:
=iferror(if(INDEX(Table2[Kommentar];[@[Radnr.]])="","",INDEX(Table2[Kommentar];[@[Radnr.]]),"")
It returns a zero in an instance of the indexed cell being blank, so simply say if the INDEX returns blank, then assume blank, else perform the INDEX as initially required.
You can also hide 0s under
file -> options -> advanced settings -> display options for this worksheet -> show a zero in cells that have a zero value
answered Jan 10 at 8:44
PeterHPeterH
3,51732548
Yeah, this was pretty much what I was planning on doing, I was hoping there was a way without having to repeat the formula though :-) And I wasn't aware of the third option, that may come in handy in the future, even if it isn't a perfect fit today.
– eirikdaude
Jan 11 at 7:59
add a comment |
Whenever I have this, I either use conditional formatting to hide the 0s or use an IF statement:
=iferror(if(INDEX(Table2[Kommentar];[@[Radnr.]])="","",INDEX(Table2[Kommentar];[@[Radnr.]]),"")
It returns a zero in an instance of the indexed cell being blank, so simply say if the INDEX returns blank, then assume blank, else perform the INDEX as initially required.
You can also hide 0s under
file -> options -> advanced settings -> display options for this worksheet -> show a zero in cells that have a zero value
answered Jan 10 at 8:44
PeterHPeterH
3,51732548
Whenever I have this, I either use conditional formatting to hide the 0s or use an IF statement:
=iferror(if(INDEX(Table2[Kommentar];[@[Radnr.]])="","",INDEX(Table2[Kommentar];[@[Radnr.]]),"")
It returns a zero in an instance of the indexed cell being blank, so simply say if the INDEX returns blank, then assume blank, else perform the INDEX as initially required.
You can also hide 0s under
file -> options -> advanced settings -> display options for this worksheet -> show a zero in cells that have a zero value
answered Jan 10 at 8:44
PeterHPeterH
3,51732548
answered Jan 10 at 8:44
PeterHPeterH
3,51732548
answered Jan 10 at 8:44
PeterHPeterH
3,51732548
answered Jan 10 at 8:44
PeterHPeterH
3,51732548
3,51732548
Yeah, this was pretty much what I was planning on doing, I was hoping there was a way without having to repeat the formula though :-) And I wasn't aware of the third option, that may come in handy in the future, even if it isn't a perfect fit today.
– eirikdaude
Jan 11 at 7:59
add a comment |
Yeah, this was pretty much what I was planning on doing, I was hoping there was a way without having to repeat the formula though :-) And I wasn't aware of the third option, that may come in handy in the future, even if it isn't a perfect fit today.
– eirikdaude
Jan 11 at 7:59
Yeah, this was pretty much what I was planning on doing, I was hoping there was a way without having to repeat the formula though :-) And I wasn't aware of the third option, that may come in handy in the future, even if it isn't a perfect fit today.
– eirikdaude
Jan 11 at 7:59
Yeah, this was pretty much what I was planning on doing, I was hoping there was a way without having to repeat the formula though :-) And I wasn't aware of the third option, that may come in handy in the future, even if it isn't a perfect fit today.
– eirikdaude
Jan 11 at 7:59
add a comment |
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%2f1392649%2favoid-having-0-returned-by-index-displayed%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
