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?

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
    Nov 14 '18 at 8:38
  • @fixer1234 I cannot understand your question. Can you please explain.
    – rukuto
    Dec 29 '18 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
    Dec 29 '18 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
    Dec 29 '18 at 22:15
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.