Conditional Number Formatting
I have a column with numberical values that range from 0 to 1,000,000,000+. I would like to format the cells to abbreviate based on their value. So far I have this:
[>999999999]0.0,,," B";[>999999]0.0,," M";0," K"
and that works, but I'd like to add in a condition for anything less than 1000 to show up as NA. So far nothing I have tried works. Any ideas?
microsoft-excel conditional-formatting
edited Feb 15 at 17:53
Leonardo Alves Machado
14510
asked Feb 15 at 16:38
mvfazio87mvfazio87
33
|
show 1 more comment
I have a column with numberical values that range from 0 to 1,000,000,000+. I would like to format the cells to abbreviate based on their value. So far I have this:
[>999999999]0.0,,," B";[>999999]0.0,," M";0," K"
and that works, but I'd like to add in a condition for anything less than 1000 to show up as NA. So far nothing I have tried works. Any ideas?
microsoft-excel conditional-formatting
edited Feb 15 at 17:53
Leonardo Alves Machado
14510
asked Feb 15 at 16:38
mvfazio87mvfazio87
33
What version of excel are you using? Older versions are limited to 3 conditions. See here for a similar question. Using 3 conditions, the following works [>999999]#,,"M";[>999]#,"K";"NA", if you try to add another, Excel barks out an error. You might be able to use a formula though if you want to have a second column.
– BlueGI
Feb 15 at 17:17
Actually, that link I posted was more for the Conditional Formatting Rules, but it lead me to test the maximum number of conditions for the number format. It seems there is a fixed number of parts where normally it would be "positive format;negative format;zero format;text format", and what you're trying is just overriding one of those parts. But there's something quirky happening if you try to use 4 parts when not a normal format codes.
– BlueGI
Feb 15 at 17:41
@BlueGI, I'm using Excel 2016. I was afraid that the condition limit might be the problem, and I think that could be it because anything I try spits out an error.
– mvfazio87
Feb 15 at 17:58
I don't think that's it, isn't the problem just that changing a numerical value to NA isn't formatting? Conditional formatting won't change your cell values, just your formatting.
– Alex M
Feb 15 at 18:44
1
@AlexM, The OP is not changing the value of the cell, just what is displayed. Try this custom number format for displaying only numbers from one to ten [>10]"Too Big";[<1]"Too Small";0
– Ted D.
Feb 15 at 21:37
|
show 1 more comment
I have a column with numberical values that range from 0 to 1,000,000,000+. I would like to format the cells to abbreviate based on their value. So far I have this:
[>999999999]0.0,,," B";[>999999]0.0,," M";0," K"
and that works, but I'd like to add in a condition for anything less than 1000 to show up as NA. So far nothing I have tried works. Any ideas?
microsoft-excel conditional-formatting
edited Feb 15 at 17:53
Leonardo Alves Machado
14510
asked Feb 15 at 16:38
mvfazio87mvfazio87
33
I have a column with numberical values that range from 0 to 1,000,000,000+. I would like to format the cells to abbreviate based on their value. So far I have this:
[>999999999]0.0,,," B";[>999999]0.0,," M";0," K"
and that works, but I'd like to add in a condition for anything less than 1000 to show up as NA. So far nothing I have tried works. Any ideas?
microsoft-excel conditional-formatting
microsoft-excel conditional-formatting
edited Feb 15 at 17:53
Leonardo Alves Machado
14510
asked Feb 15 at 16:38
mvfazio87mvfazio87
33
edited Feb 15 at 17:53
Leonardo Alves Machado
14510
asked Feb 15 at 16:38
mvfazio87mvfazio87
33
edited Feb 15 at 17:53
Leonardo Alves Machado
14510
edited Feb 15 at 17:53
Leonardo Alves Machado
14510
edited Feb 15 at 17:53
Leonardo Alves Machado
14510
14510
asked Feb 15 at 16:38
mvfazio87mvfazio87
33
asked Feb 15 at 16:38
mvfazio87mvfazio87
33
asked Feb 15 at 16:38
mvfazio87mvfazio87
33
33
What version of excel are you using? Older versions are limited to 3 conditions. See here for a similar question. Using 3 conditions, the following works [>999999]#,,"M";[>999]#,"K";"NA", if you try to add another, Excel barks out an error. You might be able to use a formula though if you want to have a second column.
– BlueGI
Feb 15 at 17:17
Actually, that link I posted was more for the Conditional Formatting Rules, but it lead me to test the maximum number of conditions for the number format. It seems there is a fixed number of parts where normally it would be "positive format;negative format;zero format;text format", and what you're trying is just overriding one of those parts. But there's something quirky happening if you try to use 4 parts when not a normal format codes.
– BlueGI
Feb 15 at 17:41
@BlueGI, I'm using Excel 2016. I was afraid that the condition limit might be the problem, and I think that could be it because anything I try spits out an error.
– mvfazio87
Feb 15 at 17:58
I don't think that's it, isn't the problem just that changing a numerical value to NA isn't formatting? Conditional formatting won't change your cell values, just your formatting.
– Alex M
Feb 15 at 18:44
1
@AlexM, The OP is not changing the value of the cell, just what is displayed. Try this custom number format for displaying only numbers from one to ten [>10]"Too Big";[<1]"Too Small";0
– Ted D.
Feb 15 at 21:37
|
show 1 more comment
What version of excel are you using? Older versions are limited to 3 conditions. See here for a similar question. Using 3 conditions, the following works [>999999]#,,"M";[>999]#,"K";"NA", if you try to add another, Excel barks out an error. You might be able to use a formula though if you want to have a second column.
– BlueGI
Feb 15 at 17:17
Actually, that link I posted was more for the Conditional Formatting Rules, but it lead me to test the maximum number of conditions for the number format. It seems there is a fixed number of parts where normally it would be "positive format;negative format;zero format;text format", and what you're trying is just overriding one of those parts. But there's something quirky happening if you try to use 4 parts when not a normal format codes.
– BlueGI
Feb 15 at 17:41
@BlueGI, I'm using Excel 2016. I was afraid that the condition limit might be the problem, and I think that could be it because anything I try spits out an error.
– mvfazio87
Feb 15 at 17:58
I don't think that's it, isn't the problem just that changing a numerical value to NA isn't formatting? Conditional formatting won't change your cell values, just your formatting.
– Alex M
Feb 15 at 18:44
1
@AlexM, The OP is not changing the value of the cell, just what is displayed. Try this custom number format for displaying only numbers from one to ten [>10]"Too Big";[<1]"Too Small";0
– Ted D.
Feb 15 at 21:37
What version of excel are you using? Older versions are limited to 3 conditions. See here for a similar question. Using 3 conditions, the following works [>999999]#,,"M";[>999]#,"K";"NA", if you try to add another, Excel barks out an error. You might be able to use a formula though if you want to have a second column.
– BlueGI
Feb 15 at 17:17
What version of excel are you using? Older versions are limited to 3 conditions. See here for a similar question. Using 3 conditions, the following works [>999999]#,,"M";[>999]#,"K";"NA", if you try to add another, Excel barks out an error. You might be able to use a formula though if you want to have a second column.
– BlueGI
Feb 15 at 17:17
Actually, that link I posted was more for the Conditional Formatting Rules, but it lead me to test the maximum number of conditions for the number format. It seems there is a fixed number of parts where normally it would be "positive format;negative format;zero format;text format", and what you're trying is just overriding one of those parts. But there's something quirky happening if you try to use 4 parts when not a normal format codes.
– BlueGI
Feb 15 at 17:41
Actually, that link I posted was more for the Conditional Formatting Rules, but it lead me to test the maximum number of conditions for the number format. It seems there is a fixed number of parts where normally it would be "positive format;negative format;zero format;text format", and what you're trying is just overriding one of those parts. But there's something quirky happening if you try to use 4 parts when not a normal format codes.
– BlueGI
Feb 15 at 17:41
@BlueGI, I'm using Excel 2016. I was afraid that the condition limit might be the problem, and I think that could be it because anything I try spits out an error.
– mvfazio87
Feb 15 at 17:58
@BlueGI, I'm using Excel 2016. I was afraid that the condition limit might be the problem, and I think that could be it because anything I try spits out an error.
– mvfazio87
Feb 15 at 17:58
I don't think that's it, isn't the problem just that changing a numerical value to NA isn't formatting? Conditional formatting won't change your cell values, just your formatting.
– Alex M
Feb 15 at 18:44
I don't think that's it, isn't the problem just that changing a numerical value to NA isn't formatting? Conditional formatting won't change your cell values, just your formatting.
– Alex M
Feb 15 at 18:44
1
1
@AlexM, The OP is not changing the value of the cell, just what is displayed. Try this custom number format for displaying only numbers from one to ten [>10]"Too Big";[<1]"Too Small";0
– Ted D.
Feb 15 at 21:37
@AlexM, The OP is not changing the value of the cell, just what is displayed. Try this custom number format for displaying only numbers from one to ten [>10]"Too Big";[<1]"Too Small";0
– Ted D.
Feb 15 at 21:37
|
show 1 more comment
2 Answers
2
active
oldest
votes
For Excel formatting with user specified criterion conditions the Alternative Section Arguments is used with a maximum of three sections rather than the default Standard Section Arrangement with its four sections.
(Source: SumProduct Pty Ltd.)
Multiple Number Formatting
Standard Section Arrangement (min 1 - max 4)
# of Sections - Values in sections
1 - All numerical values
2 - Non-negative numbers; negative numbers
3 - Positive numbers; negative numbers; zero values
4 - Positive numbers; negative numbers; zero values; text
Alternative Section Arguments (min 1 - max 3):
# of Sections - Section Details
1 - All numerical values
2 - Numbers meeting criterion; All other values
3 - Numbers meeting first criterion; Numbers meeting second criterion
. . . which do not meet the first criterion; All other values
answered Feb 15 at 19:15
Ted D.Ted D.
75028
Wow, I was literally on the linked page when I saw this. I think the combination approach (custom number format + condtional format) will be the best option.
– mvfazio87
Feb 15 at 19:57
add a comment |
I don't think it's possible to add any more conditions to the custom number format already used. One way is to add a conditional formatting with a custom number format such as [Red]"NA";[Red]"NA";[Red]"NA";[Red]"NA" with a formula like: =OR(NOT(ISNUMBER(A1)),A1<1000) (assuming that the cell is at A1)
answered Feb 16 at 18:00
JalalJalal
1
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%2f1406167%2fconditional-number-formatting%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
For Excel formatting with user specified criterion conditions the Alternative Section Arguments is used with a maximum of three sections rather than the default Standard Section Arrangement with its four sections.
(Source: SumProduct Pty Ltd.)
Multiple Number Formatting
Standard Section Arrangement (min 1 - max 4)
# of Sections - Values in sections
1 - All numerical values
2 - Non-negative numbers; negative numbers
3 - Positive numbers; negative numbers; zero values
4 - Positive numbers; negative numbers; zero values; text
Alternative Section Arguments (min 1 - max 3):
# of Sections - Section Details
1 - All numerical values
2 - Numbers meeting criterion; All other values
3 - Numbers meeting first criterion; Numbers meeting second criterion
. . . which do not meet the first criterion; All other values
answered Feb 15 at 19:15
Ted D.Ted D.
75028
Wow, I was literally on the linked page when I saw this. I think the combination approach (custom number format + condtional format) will be the best option.
– mvfazio87
Feb 15 at 19:57
add a comment |
For Excel formatting with user specified criterion conditions the Alternative Section Arguments is used with a maximum of three sections rather than the default Standard Section Arrangement with its four sections.
(Source: SumProduct Pty Ltd.)
Multiple Number Formatting
Standard Section Arrangement (min 1 - max 4)
# of Sections - Values in sections
1 - All numerical values
2 - Non-negative numbers; negative numbers
3 - Positive numbers; negative numbers; zero values
4 - Positive numbers; negative numbers; zero values; text
Alternative Section Arguments (min 1 - max 3):
# of Sections - Section Details
1 - All numerical values
2 - Numbers meeting criterion; All other values
3 - Numbers meeting first criterion; Numbers meeting second criterion
. . . which do not meet the first criterion; All other values
answered Feb 15 at 19:15
Ted D.Ted D.
75028
Wow, I was literally on the linked page when I saw this. I think the combination approach (custom number format + condtional format) will be the best option.
– mvfazio87
Feb 15 at 19:57
add a comment |
For Excel formatting with user specified criterion conditions the Alternative Section Arguments is used with a maximum of three sections rather than the default Standard Section Arrangement with its four sections.
(Source: SumProduct Pty Ltd.)
Multiple Number Formatting
Standard Section Arrangement (min 1 - max 4)
# of Sections - Values in sections
1 - All numerical values
2 - Non-negative numbers; negative numbers
3 - Positive numbers; negative numbers; zero values
4 - Positive numbers; negative numbers; zero values; text
Alternative Section Arguments (min 1 - max 3):
# of Sections - Section Details
1 - All numerical values
2 - Numbers meeting criterion; All other values
3 - Numbers meeting first criterion; Numbers meeting second criterion
. . . which do not meet the first criterion; All other values
answered Feb 15 at 19:15
Ted D.Ted D.
75028
For Excel formatting with user specified criterion conditions the Alternative Section Arguments is used with a maximum of three sections rather than the default Standard Section Arrangement with its four sections.
(Source: SumProduct Pty Ltd.)
Multiple Number Formatting
Standard Section Arrangement (min 1 - max 4)
# of Sections - Values in sections
1 - All numerical values
2 - Non-negative numbers; negative numbers
3 - Positive numbers; negative numbers; zero values
4 - Positive numbers; negative numbers; zero values; text
Alternative Section Arguments (min 1 - max 3):
# of Sections - Section Details
1 - All numerical values
2 - Numbers meeting criterion; All other values
3 - Numbers meeting first criterion; Numbers meeting second criterion
. . . which do not meet the first criterion; All other values
answered Feb 15 at 19:15
Ted D.Ted D.
75028
answered Feb 15 at 19:15
Ted D.Ted D.
75028
answered Feb 15 at 19:15
Ted D.Ted D.
75028
answered Feb 15 at 19:15
Ted D.Ted D.
75028
75028
Wow, I was literally on the linked page when I saw this. I think the combination approach (custom number format + condtional format) will be the best option.
– mvfazio87
Feb 15 at 19:57
add a comment |
Wow, I was literally on the linked page when I saw this. I think the combination approach (custom number format + condtional format) will be the best option.
– mvfazio87
Feb 15 at 19:57
Wow, I was literally on the linked page when I saw this. I think the combination approach (custom number format + condtional format) will be the best option.
– mvfazio87
Feb 15 at 19:57
Wow, I was literally on the linked page when I saw this. I think the combination approach (custom number format + condtional format) will be the best option.
– mvfazio87
Feb 15 at 19:57
add a comment |
I don't think it's possible to add any more conditions to the custom number format already used. One way is to add a conditional formatting with a custom number format such as [Red]"NA";[Red]"NA";[Red]"NA";[Red]"NA" with a formula like: =OR(NOT(ISNUMBER(A1)),A1<1000) (assuming that the cell is at A1)
answered Feb 16 at 18:00
JalalJalal
1
add a comment |
I don't think it's possible to add any more conditions to the custom number format already used. One way is to add a conditional formatting with a custom number format such as [Red]"NA";[Red]"NA";[Red]"NA";[Red]"NA" with a formula like: =OR(NOT(ISNUMBER(A1)),A1<1000) (assuming that the cell is at A1)
answered Feb 16 at 18:00
JalalJalal
1
add a comment |
I don't think it's possible to add any more conditions to the custom number format already used. One way is to add a conditional formatting with a custom number format such as [Red]"NA";[Red]"NA";[Red]"NA";[Red]"NA" with a formula like: =OR(NOT(ISNUMBER(A1)),A1<1000) (assuming that the cell is at A1)
answered Feb 16 at 18:00
JalalJalal
1
I don't think it's possible to add any more conditions to the custom number format already used. One way is to add a conditional formatting with a custom number format such as [Red]"NA";[Red]"NA";[Red]"NA";[Red]"NA" with a formula like: =OR(NOT(ISNUMBER(A1)),A1<1000) (assuming that the cell is at A1)
answered Feb 16 at 18:00
JalalJalal
1
answered Feb 16 at 18:00
JalalJalal
1
answered Feb 16 at 18:00
JalalJalal
1
answered Feb 16 at 18:00
JalalJalal
1
1
add a comment |
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%2f1406167%2fconditional-number-formatting%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
What version of excel are you using? Older versions are limited to 3 conditions. See here for a similar question. Using 3 conditions, the following works [>999999]#,,"M";[>999]#,"K";"NA", if you try to add another, Excel barks out an error. You might be able to use a formula though if you want to have a second column.
– BlueGI
Feb 15 at 17:17
Actually, that link I posted was more for the Conditional Formatting Rules, but it lead me to test the maximum number of conditions for the number format. It seems there is a fixed number of parts where normally it would be "positive format;negative format;zero format;text format", and what you're trying is just overriding one of those parts. But there's something quirky happening if you try to use 4 parts when not a normal format codes.
– BlueGI
Feb 15 at 17:41
@BlueGI, I'm using Excel 2016. I was afraid that the condition limit might be the problem, and I think that could be it because anything I try spits out an error.
– mvfazio87
Feb 15 at 17:58
I don't think that's it, isn't the problem just that changing a numerical value to NA isn't formatting? Conditional formatting won't change your cell values, just your formatting.
– Alex M
Feb 15 at 18:44
1
@AlexM, The OP is not changing the value of the cell, just what is displayed. Try this custom number format for displaying only numbers from one to ten [>10]"Too Big";[<1]"Too Small";0
– Ted D.
Feb 15 at 21:37