Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
ash ash is offline
external usenet poster
 
Posts: 54
Default Converting #s to specified #digits

Hi! Any help is appreciated.

I have two columns of data that represent dollar amounts. The amounts vary
across the board. Is there a formula that I could put in that would take the
numbers and change them all to be represented in a single dollar amount?

For example, if I want to represent everything in millions (Mk),
7,240,000 would be displayed as 7.2; 125,000 would be displayed as .1

I would also like to make sure it is rounding the tenths places, so that if
the first example had been 7,280,000, it would display as 7.3

If there's not a formula that can do this, could you also let me know.
Thanks!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 430
Default Converting #s to specified #digits

not sure if there is a way to actually format the cells to show this but you
could do the following.

insert a column and in the column put the following formula. =A1/1000000
then format...cells....number, 1 decimal. Then copy the formula down and you
should get the results you want. (The formula assumes your data starts in
cell A1.)

"Ash" wrote:

Hi! Any help is appreciated.

I have two columns of data that represent dollar amounts. The amounts vary
across the board. Is there a formula that I could put in that would take the
numbers and change them all to be represented in a single dollar amount?

For example, if I want to represent everything in millions (Mk),
7,240,000 would be displayed as 7.2; 125,000 would be displayed as .1

I would also like to make sure it is rounding the tenths places, so that if
the first example had been 7,280,000, it would display as 7.3

If there's not a formula that can do this, could you also let me know.
Thanks!

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Converting #s to specified #digits

Ash

Enter 1000000 in an unused cell.

Copy that cell.

Select the range of numbers and EditPaste Special(in place)DivideOKEsc.

Format to 1 decimal place.

Clear the cell with 1000000


Gord Dibben MS Excel MVP

On Wed, 21 Feb 2007 12:52:25 -0800, Ash wrote:

Hi! Any help is appreciated.

I have two columns of data that represent dollar amounts. The amounts vary
across the board. Is there a formula that I could put in that would take the
numbers and change them all to be represented in a single dollar amount?

For example, if I want to represent everything in millions (Mk),
7,240,000 would be displayed as 7.2; 125,000 would be displayed as .1

I would also like to make sure it is rounding the tenths places, so that if
the first example had been 7,280,000, it would display as 7.3

If there's not a formula that can do this, could you also let me know.
Thanks!


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default Converting #s to specified #digits

On Wed, 21 Feb 2007 12:52:25 -0800, Ash wrote:

Hi! Any help is appreciated.

I have two columns of data that represent dollar amounts. The amounts vary
across the board. Is there a formula that I could put in that would take the
numbers and change them all to be represented in a single dollar amount?

For example, if I want to represent everything in millions (Mk),
7,240,000 would be displayed as 7.2; 125,000 would be displayed as .1

I would also like to make sure it is rounding the tenths places, so that if
the first example had been 7,280,000, it would display as 7.3

If there's not a formula that can do this, could you also let me know.
Thanks!


When you write "display" I think "format".

So here's a custom format which will display the contents of the cell as you
describe:

Format/Cells/Number/Custom Type: $#,##0.0,,

If the cell contains 7,280,000, it will display as $7.3

You may omit the "$" if you wish.


--ron
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Converting #s to specified #digits

Hi,

Select format cell - Go to custom - Paste #.00,, in 'Type'. It works

Regards,
Nishad



"Ron Rosenfeld" wrote:

On Wed, 21 Feb 2007 12:52:25 -0800, Ash wrote:

Hi! Any help is appreciated.

I have two columns of data that represent dollar amounts. The amounts vary
across the board. Is there a formula that I could put in that would take the
numbers and change them all to be represented in a single dollar amount?

For example, if I want to represent everything in millions (Mk),
7,240,000 would be displayed as 7.2; 125,000 would be displayed as .1

I would also like to make sure it is rounding the tenths places, so that if
the first example had been 7,280,000, it would display as 7.3

If there's not a formula that can do this, could you also let me know.
Thanks!


When you write "display" I think "format".

So here's a custom format which will display the contents of the cell as you
describe:

Format/Cells/Number/Custom Type: $#,##0.0,,

If the cell contains 7,280,000, it will display as $7.3

You may omit the "$" if you wish.


--ron

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
last ten digits Martina Excel Worksheet Functions 5 December 1st 06 05:53 AM
16 digits Jane Excel Discussion (Misc queries) 10 January 20th 06 12:51 PM
Converting Dates to 8 digits jermsalerms Excel Worksheet Functions 17 January 20th 06 03:00 AM
2 DIGITS OUT OF 4 gregork Excel Discussion (Misc queries) 9 February 20th 05 04:30 PM
2 DIGITS OUT OF 4 Knoxi Excel Discussion (Misc queries) 0 February 19th 05 06:54 PM


All times are GMT +1. The time now is 05:30 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"