How to display 1,000,000 as 1M and then later use it in sum function as a
million? Thank you

To display 1,000,000 as 1M in Microsoft Excel, you can use a custom number format. Here's how:

1. Select the cell or range of cells that you want to format.
2. Right-click and select "Format Cells" from the context menu.
3. In the Format Cells dialog box, select "Custom" from the Category list.
4. In the Type field, enter the following format code:
   Code:

   #,##0,,"M"

5. Click "OK" to apply the format.

Now, any number you enter in the selected cells that is greater than or equal to 1,000,000 will be displayed as 1M.

To use the formatted number in a sum function as a million, you can use a formula to convert it back to its original value. Here's an example:

1. Enter the numbers you want to sum in a column or row.
2. In the cell where you want to display the sum, enter the following formula:
   Code:

   =SUM(A1:A10)/1000000

3. Replace "A1:A10" with the range of cells that contain the numbers you want to sum.
4. Press Enter to calculate the sum.

The formula divides the sum of the selected cells by 1,000,000 to convert it back to millions. The result will be displayed in the cell with the formula.

Lana,

What you are looking for is to change the number format of the cell.

To accomplish this go to FormatCells...([Ctrl] + 1)Number tab, under
"Category:" select "Custom" (last item in the list), in the "Type" text box
type your number format.

The following is a list of number formats and how the value of 1,000,000
will be displayed (keep in mind there are 4 parts (positive, negative, zero,
and text) to the number format and I'm only showing you the positve number
format);

#,##0.00 1,000,000.00
#,##0 1,000,000
0, 1000
#,##0, 1,000
0,, 1
0,,\M 1M
0.00,,\M 1.00M
0,," Mil" 1 Mil
0.00,," Mil" 1.00 Mil

All of these are different ways of displaying the value 1,000,000. You will
still enter it as a 1 followed by 6 zeros and it will be the value of
1,000,000 in any calculation you use this/these cell(s) in.

Please go to Help and look up number formats to learn what all of the
different codes mean and how to format each of the 4 parts of the number
format.

HTH,

Conan Kelly

"Lana" wrote in message
...
How to display 1,000,000 as 1M and then later use it in sum function as a
million? Thank you

On Fri, 3 Aug 2007 13:38:01 -0700, Lana wrote:

How to display 1,000,000 as 1M and then later use it in sum function as a
million? Thank you

Format/Cells/Number/Custom Type: [=1000000]#,,\M;General


But that's an odd question.

The above will display 1,000,000 as 1M and will store it as 1,000,000 that can
be used in any other arithmetic function.

Since you did not specify how you want any OTHER numbers displayed, they will
be displayed as General format.

Rather than my guessing, you might want to rethink your specifications.


--ron

If you haven't had this answer yet:

Choose Format, Cells, Number tab, Custom and enter the following on the Type
line:

#,,"M"

or
0,,"M"

--
Cheers,
Shane Devenshire


"Lana" wrote:

How to display 1,000,000 as 1M and then later use it in sum function as a
million? Thank you