Keeping a fixed constant (like letter “M”) and a variable (like a number “100”) in the same cell in...
In excel, is there a method where a constant term can be fixed in the cell and I only need to input the variable term.
(this is difficult to put in words)
example:
So, I have different values, like 100M, 50M, 30M, 40M, etc.
Now, can I keep the "M" fixed so that I only need to input the numerical value?
Like I just type "100" but it shows "100M"?
Is it also possible to have a formula that ignores the "M"?
Like, A1=100M, B1=30M. Now, I want C1=A1-B1 to be "70M" instead of "#value!"?
Is this possible to do if I can keep the "M" as a fixed value instead of manually inputting it?
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
asked Nov 13 at 7:42
user28219
358125
add a comment |
In excel, is there a method where a constant term can be fixed in the cell and I only need to input the variable term.
(this is difficult to put in words)
example:
So, I have different values, like 100M, 50M, 30M, 40M, etc.
Now, can I keep the "M" fixed so that I only need to input the numerical value?
Like I just type "100" but it shows "100M"?
Is it also possible to have a formula that ignores the "M"?
Like, A1=100M, B1=30M. Now, I want C1=A1-B1 to be "70M" instead of "#value!"?
Is this possible to do if I can keep the "M" as a fixed value instead of manually inputting it?
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
asked Nov 13 at 7:42
user28219
358125
Is the M just a format character or does the value actually need to contain it (e.g., will you be testing against the value, doing lookups, etc.)?
– fixer1234
Nov 14 at 8:38
@fixer1234 I cannot understand your question. Can you please explain.
– user28219
18 hours ago
If all you need is for the contents to be displayed with the M, it can be just formatting. That isn't part of the cell content, it's only decoration. But if you will be using the M as part of the content, it needs to actually be included in the content. For example, if you will be testing the cell, likeIF(A10="100M",...)orVLOOKUP("100M",A1:A50...). If the M is just formatting decoration, the actual content will just be a number, so you can do arithmetic with it and format the result the same way. (cont'd)
– fixer1234
10 hours ago
But if you need to use the M as part of the actual content, like those examples, you need to jump through hoops to extract the numerical content for the arithmetic, then reconstruct the finished cell. That requires a very different solution.
– fixer1234
10 hours ago
add a comment |
In excel, is there a method where a constant term can be fixed in the cell and I only need to input the variable term.
(this is difficult to put in words)
example:
So, I have different values, like 100M, 50M, 30M, 40M, etc.
Now, can I keep the "M" fixed so that I only need to input the numerical value?
Like I just type "100" but it shows "100M"?
Is it also possible to have a formula that ignores the "M"?
Like, A1=100M, B1=30M. Now, I want C1=A1-B1 to be "70M" instead of "#value!"?
Is this possible to do if I can keep the "M" as a fixed value instead of manually inputting it?
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
asked Nov 13 at 7:42
user28219
358125
In excel, is there a method where a constant term can be fixed in the cell and I only need to input the variable term.
(this is difficult to put in words)
example:
So, I have different values, like 100M, 50M, 30M, 40M, etc.
Now, can I keep the "M" fixed so that I only need to input the numerical value?
Like I just type "100" but it shows "100M"?
Is it also possible to have a formula that ignores the "M"?
Like, A1=100M, B1=30M. Now, I want C1=A1-B1 to be "70M" instead of "#value!"?
Is this possible to do if I can keep the "M" as a fixed value instead of manually inputting it?
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
asked Nov 13 at 7:42
user28219
358125
asked Nov 13 at 7:42
user28219
358125
asked Nov 13 at 7:42
user28219
358125
asked Nov 13 at 7:42
user28219
358125
asked Nov 13 at 7:42
user28219
358125
358125
Is the M just a format character or does the value actually need to contain it (e.g., will you be testing against the value, doing lookups, etc.)?
– fixer1234
Nov 14 at 8:38
@fixer1234 I cannot understand your question. Can you please explain.
– user28219
18 hours ago
If all you need is for the contents to be displayed with the M, it can be just formatting. That isn't part of the cell content, it's only decoration. But if you will be using the M as part of the content, it needs to actually be included in the content. For example, if you will be testing the cell, likeIF(A10="100M",...)orVLOOKUP("100M",A1:A50...). If the M is just formatting decoration, the actual content will just be a number, so you can do arithmetic with it and format the result the same way. (cont'd)
– fixer1234
10 hours ago
But if you need to use the M as part of the actual content, like those examples, you need to jump through hoops to extract the numerical content for the arithmetic, then reconstruct the finished cell. That requires a very different solution.
– fixer1234
10 hours ago
add a comment |
Is the M just a format character or does the value actually need to contain it (e.g., will you be testing against the value, doing lookups, etc.)?
– fixer1234
Nov 14 at 8:38
@fixer1234 I cannot understand your question. Can you please explain.
– user28219
18 hours ago
If all you need is for the contents to be displayed with the M, it can be just formatting. That isn't part of the cell content, it's only decoration. But if you will be using the M as part of the content, it needs to actually be included in the content. For example, if you will be testing the cell, likeIF(A10="100M",...)orVLOOKUP("100M",A1:A50...). If the M is just formatting decoration, the actual content will just be a number, so you can do arithmetic with it and format the result the same way. (cont'd)
– fixer1234
10 hours ago
But if you need to use the M as part of the actual content, like those examples, you need to jump through hoops to extract the numerical content for the arithmetic, then reconstruct the finished cell. That requires a very different solution.
– fixer1234
10 hours ago
Is the M just a format character or does the value actually need to contain it (e.g., will you be testing against the value, doing lookups, etc.)?
– fixer1234
Nov 14 at 8:38
Is the M just a format character or does the value actually need to contain it (e.g., will you be testing against the value, doing lookups, etc.)?
– fixer1234
Nov 14 at 8:38
@fixer1234 I cannot understand your question. Can you please explain.
– user28219
18 hours ago
@fixer1234 I cannot understand your question. Can you please explain.
– user28219
18 hours ago
If all you need is for the contents to be displayed with the M, it can be just formatting. That isn't part of the cell content, it's only decoration. But if you will be using the M as part of the content, it needs to actually be included in the content. For example, if you will be testing the cell, like
IF(A10="100M",...) or VLOOKUP("100M",A1:A50...). If the M is just formatting decoration, the actual content will just be a number, so you can do arithmetic with it and format the result the same way. (cont'd)– fixer1234
10 hours ago
If all you need is for the contents to be displayed with the M, it can be just formatting. That isn't part of the cell content, it's only decoration. But if you will be using the M as part of the content, it needs to actually be included in the content. For example, if you will be testing the cell, like
IF(A10="100M",...) or VLOOKUP("100M",A1:A50...). If the M is just formatting decoration, the actual content will just be a number, so you can do arithmetic with it and format the result the same way. (cont'd)– fixer1234
10 hours ago
But if you need to use the M as part of the actual content, like those examples, you need to jump through hoops to extract the numerical content for the arithmetic, then reconstruct the finished cell. That requires a very different solution.
– fixer1234
10 hours ago
But if you need to use the M as part of the actual content, like those examples, you need to jump through hoops to extract the numerical content for the arithmetic, then reconstruct the finished cell. That requires a very different solution.
– fixer1234
10 hours ago
add a comment |
1 Answer
1
active
oldest
votes
select all the cell you need.. r-click > format cell > custom : #,##0.00 [$M]
done.
If you don't need the comma separator or decimal.. just put 0 [$M] will do. If without the space between the number and 'M' .. then use 0[$M]
hope it helps. ( :
p/s: Idea source = setting the 'M' as a currency character.
answered Nov 13 at 8:03
p._phidot_
56429
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
select all the cell you need.. r-click > format cell > custom : #,##0.00 [$M]
done.
If you don't need the comma separator or decimal.. just put 0 [$M] will do. If without the space between the number and 'M' .. then use 0[$M]
hope it helps. ( :
p/s: Idea source = setting the 'M' as a currency character.
answered Nov 13 at 8:03
p._phidot_
56429
add a comment |
select all the cell you need.. r-click > format cell > custom : #,##0.00 [$M]
done.
If you don't need the comma separator or decimal.. just put 0 [$M] will do. If without the space between the number and 'M' .. then use 0[$M]
hope it helps. ( :
p/s: Idea source = setting the 'M' as a currency character.
answered Nov 13 at 8:03
p._phidot_
56429
add a comment |
select all the cell you need.. r-click > format cell > custom : #,##0.00 [$M]
done.
If you don't need the comma separator or decimal.. just put 0 [$M] will do. If without the space between the number and 'M' .. then use 0[$M]
hope it helps. ( :
p/s: Idea source = setting the 'M' as a currency character.
answered Nov 13 at 8:03
p._phidot_
56429
select all the cell you need.. r-click > format cell > custom : #,##0.00 [$M]
done.
If you don't need the comma separator or decimal.. just put 0 [$M] will do. If without the space between the number and 'M' .. then use 0[$M]
hope it helps. ( :
p/s: Idea source = setting the 'M' as a currency character.
answered Nov 13 at 8:03
p._phidot_
56429
answered Nov 13 at 8:03
p._phidot_
56429
answered Nov 13 at 8:03
p._phidot_
56429
answered Nov 13 at 8:03
p._phidot_
56429
56429
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f1374945%2fkeeping-a-fixed-constant-like-letter-m-and-a-variable-like-a-number-100%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
Is the M just a format character or does the value actually need to contain it (e.g., will you be testing against the value, doing lookups, etc.)?
– fixer1234
Nov 14 at 8:38
@fixer1234 I cannot understand your question. Can you please explain.
– user28219
18 hours ago
If all you need is for the contents to be displayed with the M, it can be just formatting. That isn't part of the cell content, it's only decoration. But if you will be using the M as part of the content, it needs to actually be included in the content. For example, if you will be testing the cell, like
IF(A10="100M",...)orVLOOKUP("100M",A1:A50...). If the M is just formatting decoration, the actual content will just be a number, so you can do arithmetic with it and format the result the same way. (cont'd)– fixer1234
10 hours ago
But if you need to use the M as part of the actual content, like those examples, you need to jump through hoops to extract the numerical content for the arithmetic, then reconstruct the finished cell. That requires a very different solution.
– fixer1234
10 hours ago