0

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?

Improve this question
4
  • 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
    Commented Nov 14, 2018 at 8:38
  • @fixer1234 I cannot understand your question. Can you please explain.
    – rukuto
    Commented Dec 29, 2018 at 13:43
  • 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
    Commented Dec 29, 2018 at 22:15
  • 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
    Commented Dec 29, 2018 at 22:15

1 Answer 1

Reset to default
2

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .