Need to round up 27,890,000 to 28 in a spreadsheet - PLEASE 2

[PeanutB7]

I am working on a spreadsheet in which I would like to count all in millions. Therefore I need to round up all numbers to reflect millions and billions only. For example a number like 27,890,000 would be shown in my spreadsheet cell as only 28. I would prefer not to have to create a new column of rounded numbers but would prefer reformating all cells for the rounding effect. I have seen how to use roundup but it still displays 27,890,000 using =roundup(Cell No.,-6) as 28,000,000. For simplicity sake I would prefer to eliminat the needless zeros.

Thank you in advance for your time,

JB

 

[bluedragon2]

=ROUNDUP(A1/1000000,0)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[KenWright]

Just select your numbers and put a custom format on. Pick any of the usual and then hit custom and add a couple of commas to the end and this will suppress 3 zeroes per comma.

eg instead of a format of

#,##0

use

#,##0,,

This will turn 12,345,678 into 12

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[AnotherHiggins]

I thought there was a custom format way to do this, but couldn't sort it out.

Thanks, Ken!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[KenWright]

Cheers John,

The other one I like is if you want to suppress the data entirely, so that even if you drag across it (if you had formatted the text white for example), you still won't see it.

Just apply a format of ;;;

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Gavona]

Just to be clear for anyone else who reads the post: Roundup actually rounds the numbers, the format solutions merely round what is displayed. So if the original sum was 4,300,000+4,300,000 =8,600,000 :

Using bluedragon's roundup solution: 5+5=10

Substituting Round for roundup you would get: 4+4=8

Using Ken's format solution: 4+4=9
This preserves a greater accuracy in the total - and is clearly what Jb is looking for. (But I have had people getting quite upset and critical of my apparent inability to do maths!)


Gavin