#### Rounding numbers to the nearest thousand

```Simple question (i hope)

Is there a simple way i can round numbers to the nearest thousand by using
the format cells command.

For example
i would like to display 1,234.56 as 12

I don't want to be diving by 1000 and rounding to zero decimal places
because i still want the whole number displayed in the formula bar when
selecting it.

i use excel 2003

thanx
Mark
```
 0
mark564 (750)
3/9/2005 3:31:06 PM
excel.misc 78881 articles. 5 followers.

2 Replies
752 Views

Similar Articles

[PageSpeed] 2

```Format the number as #,##0,;-#,##0, or similar. Not the comma at the end -
this tells Excel to display as thousands (two commas here will display as
millions).

Note that 1,234.56 will display as 1 (thousand) not 12.

"Mark" wrote:

> Simple question (i hope)
>
> Is there a simple way i can round numbers to the nearest thousand by using
> the format cells command.
>
> For example
> i would like to display 1,234.56 as 12
>
> I don't want to be diving by 1000 and rounding to zero decimal places
> because i still want the whole number displayed in the formula bar when
> selecting it.
>
> i use excel 2003
>
> thanx
> Mark
```
 0
Vicar (5)
3/9/2005 3:49:17 PM
```You will actually have to create your own custom number format, which is
"#,".  And yes, 1234,56 will then display as 1. 1501 will display as 2

"Vicar" wrote:

> Format the number as #,##0,;-#,##0, or similar. Not the comma at the end -
> this tells Excel to display as thousands (two commas here will display as
> millions).
>
> Note that 1,234.56 will display as 1 (thousand) not 12.
>
> "Mark" wrote:
>
> > Simple question (i hope)
> >
> > Is there a simple way i can round numbers to the nearest thousand by using
> > the format cells command.
> >
> > For example
> > i would like to display 1,234.56 as 12
> >
> > I don't want to be diving by 1000 and rounding to zero decimal places
> > because i still want the whole number displayed in the formula bar when
> > selecting it.
> >
> > i use excel 2003
> >
> > thanx
> > Mark
```
 0
Kassie (262)
3/10/2005 12:13:02 PM

Similar Artilces:

LOST CD KEY NUMBER
help, i lost the original case the install cd came with for microsoft publisher. it requires the cd key number to install. what am i suppose to do now without the cd case or box the software came in? i thought of looking in the disk under different files to find a source for the number but came up empty. maybe i registered my program when i purchased it, but how do i track down the original registration info and get microsoft to give me an install cd key number so i can reinstall it on my new computer? any suggestions? fred How to Obtain a New Product Key for Office Program Setup htt...

Formula to Convert Numbers to Words
What is the formula to convert numbers to words? Example: Number Words 568 Five Hundred Sixty Eight There is no direct functions to convert this. For a VBA solution check out the below links http://support.microsoft.com/kb/213360 http://www.ozgrid.com/VBA/ValueToWords.htm http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "Kiley" wrote: > What is the formula to convert numbers to words? > > Example: > > Number Words > 568 Five Hundred Si...

letter to number formulas
hi, I'm having trouble finding a way to formulate a cell so when colleague enters a Letter a specifiic cell would display a number valu relating to that letter. For example is the Letter M = 5, then when a colleague enters a M i say A1, a number 5 would automatical be added to A2 I hope I'm clear enough!!! Cheer -- wildoma ----------------------------------------------------------------------- wildomac's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1681 View this thread: http://www.excelforum.com/showthread.php?threadid=32012 Hi as a starting poi...

rounding #9
is there a way to have a column round automatically instead of having to round onto another column? I don't want to add another column to just be able to round. -- denhar ------------------------------------------------------------------------ denhar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24089 View this thread: http://www.excelforum.com/showthread.php?threadid=378356 If the column contains formulas, the formulas can incorporate the Round() function within themselves. For example, if the formula is: =A1*B1 it could be revised to: =ROUND(A1*...

PPT 07 from PPT03 imported slides doe not 'insert' slide number
Since migrating to MS Office 2007, my old slides when upgraded to PPT 07 will not allowme to add a page numberon the slides. I have saved the slide show to a new show, I have tried placin gin the field on Master View, I have simply tied the 'button' to insert slide number. I get no results, after multiple multiple efforts. Anyone fiind a work around? I do not need to re-build 60-slides for training just to have a page number. Hi, You need to ensure that the slide number placeholder is set in the master template. Slide master/ master layout/ tick slide number >...

Best Practice for large number of checkbox/bit attributes
I am working on a deployment of CRM 3.0 for my company and one of the requirements is to track a large number of checkboxes (bit fields) for contacts. These are used to track skill sets, certifications, and other similar data. There are about 125 of these items and they could add to this list over time. Looking at this requirement from my ASP.NET background, I would easily solve this with a lookup table of "skills" with all the skills listed and allow the user to add skills and populate a "user_skills" table based on what they added. However, I just can't get my min...

Stored Procudures to increment invoice and payment numbers
Hi, Im a GP-econnect newbie, I wanted to know if there where stored pro that increment invoice, check, and payment numbers? or does anyone know what table they are stored in? I am trying to avoid duplicates. There is an assembly, Microsoft.GreatPlains.eConnect.MiscRoutines, sorry I cannot recall the name precisely. You can call a method called getNextSopNumber to get next sop number. If you don't roll back failed trans with another method in the assembly, no problem. There is a stored proc., sopGetIDNumber that lock sop40200 while updating it. You can search the group for my posts ...

round() ?
Hello How can i round float to 2 digits after '.' ? Thanx Quick and Dirty: Multiply by 100, add 0.5 (to round up correctly). convert to long, then back to float, then divide by 100. This is limited to values that will fit within a long, and is not efficient. It is reliable. also: float val; val = val - fmod(val, .01); Or, the real thing: char *_fcvt( double value, int count, int *dec, int *sign ); Which is a char string, which you can then feed to atof() and get your rounded value back. Alas, beware errors in precision limits! Balboos user@domain.invalid wrote: >...

Rounding question
I'm using Excel 2007 but 'save as' 97-2003 as colleagues have different versions. All number formats are set to 2 decimal places. I'm finding that percentage calculations are rounding up to 2 decimal places but when the result in currency is subtracted from another figure the sum is rounded down. This gives results such as 6 - 3 = 4. I'm sure there must be a simple answer but I can't find it. Hoping someone here knows, as checking all simple calcuations is getting ridiculous ! Carrie "Carrie" <pantscarolyn.smith2@ntlworld.com> w...

Error number 0x80000003
Hello, Have XP Home Edition,Service Pack 3..I can only use safe mode to a limited degree..The Internet will freexe in short order...Getting to desktop a box will say MSASCui.exe application error 0x80000003..ISP told me I have spyware and malware..Any fix here short of using the Restore disk by wiping out the hard drive...Thanks,Nick NICK MEALE wrote: > Hello, > Have XP Home Edition,Service Pack 3..I can only use safe mode to a limited > degree..The Internet will freexe in short order...Getting to desktop a box > will say MSASCui.exe application error...

auot numbering
hi all,i have made an invoicve and saved as a template.is there a way to get a sequential number each time an invoice is opend and saved as a customer name?any help would be appreciated.thx wyn -- WYN someone had a similar question about automatic sequencing. here is th code someone posted for that query: Private Sub Workbook_Open() Const DEFAULTSTART As Integer = 1 Const MYAPPLICATION As String = "Excel" Const MYSECTION As String = "myInvoice" Const MYKEY As String = "myInvoiceKey" Const MYLOCATION As String = "A1" Dim regValue As Long With ThisW...

Problem with rounding currency values
Hi, I am using the Sales Global Procedure SOP_Calculate_Trade_Discount_Split. This procedure returns the line trade discount. The line trade discount returned is rounded off. eg: if value is 6.76 then it returns 7.00. Is there some place where i can specify to turn off the rounding off? The output type is Currency. Any help is greatly appreciated. Thanks & Regards, Sup ...

Print Item Number Ole object on Sales Order Processing reports
Would like the ability to print an Item Number Ole object on my SOP reports. For example, go to Transactions, Sales, Sales Transaction Entry. In the Sales Transaction Entry window, enter an Order for an Item. Click the Item Number Note button. Once you are in the Note window, click the paperclip button to add an Ole Object. In the OLE Container window, click Edit, and then click Insert New Object. In the next widow click Create from File. This is where you will attach an existing object (such as an image or text file). Close the window and save your changes. Click Attach to atta...

how do i number tickets in publisher
i am trying to create tickets and need to number them. is there a way to do it in publisher rather than by hand A small child turns to Ed, and exclaims: "Look! Look! A post from lujiraj <lujiraj@discussions.microsoft.com>!"... > i am trying to create tickets and need to number them. is there a > way to do it in publisher rather than by hand Try http://www.publishermvps.com/Default.aspx?tabid=95 -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer: http://mvps.org/the_nerd/disclaim.htm ...

Auto Creation of a Customer Number
Good day! I am working on some interfaces between our Great Plains application and our Line of Business application. One of the requests that came up was the ability to have a customer number created automatically when we create a customer. Rather than have the Great Plains user enter a number that may or may not already exist, our hope is that the application would look in the existing table of customers, and using a select max(custnmbr), add 1 to the existing max value, and then place it into the customer number field. I was thinking that I would use a trigger on the insert of the...

page number
How do I remove page number from single pages, not only frontpage You can either insert a section break either side the un-numbered page and change the numbering in the new section or you can conditionally insert the page number in all the pages using fields e.g. {IF {Page} <> 4 "{Page}"} will insert the page number on all pages except page 4. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mv...

Format Numbers
How do I set up numbers to format different colors depending on +/- in time calculations? Left out that I need them to change automaticlly "DMJohnson" wrote: > How do I set up numbers to format different colors depending on +/- in time > calculations? Have a look at Data/Conditional Formatting. HTH J -- pinmaste ----------------------------------------------------------------------- pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=626 View this thread: http://www.excelforum.com/showthread.php?threadid=49742 ...

Round Up and Round Down Time
Good Morning & a Happy New Year to all subscribers in this group. I want to round up Start times and round down Finish times to the next or previous 15 minutes in my spreadsheet. I have found MROUND but this does not work for me in all cases. There does not seem to be MROUNDUP or MROUNDDOWN available. I can get the results I want using a VLOOKUP table but is there another way? Regards to all, Dave Moore =FLOOR(A1,TIME(,15,)) =CEILING(A1,TIME(,15,)) -- David Biddulph DaveMoore wrote: > Good Morning & a Happy New Year to all subscribers in this group. > >...

How do I change column labels from numbers to letters in Excel?
In Excel, my column headers somehow changed from letters to numbers. How do I get the letter headings back? For example, I can no longer reference cell C3 because the C is now a number. Hi Allison, Try: Tools | Options | General | Check R1C1 Reference Style --- Regards, Norman "AllisonCincy" <AllisonCincy@discussions.microsoft.com> wrote in message news:12E43CB2-78D6-49D7-9584-4AD3DC34DDFB@microsoft.com... > In Excel, my column headers somehow changed from letters to numbers. How > do > I get the letter headings back? > > For example, I can no lon...

Automatic Rounding problems
I am using Money 99 and, only on investments section, when I try enter a price or quantity with a decimal part, it simply round it to an integer number automatically when I exit the edit box. Do you know how can I make it work correctly. My Contorl Panel / Currency is already configured to 2 decimal digits and it works perfectly in the other fields where I have to put a value. Thanks for your help. Fabio Wasn't Money 99 around before they decimalized stocks? May not be possible to fix it... Fabio Pires wrote: > I am using Money 99 and, only on investments section, > when...

How can I change commision % after it pass certian number
i have class project where i am suppose to be sales manger and i am trying to set up a pay plan for sale personals, the gross profit percentage changes "20%" after the 12 sale the plan retroactive and go up to 25%. i am trying to think if there is plan that changes the commission after it hits that magic number Thanks Hi Ab shihab, Not exactly sure what you want, but, here is a first try, Gross Profit in A1 Number of sales in B1 Put this in C1 =IF(B1<=12,A1*0.2,A1*0.25) HTH Martin "Ab_shihab" <Ab_shihab@discussions.microsoft.com> wrote in message news...

sorting numbers #4
i have a simple sheet that basically consists of name, ip address, and then other columns obviously when i sort by ip address the .1,.2,.3,.4,.5, etc get sorted in between the higher numbers ie: 99 100 1 101 102 and i have to manually move them i cant seem to see how you can sort within a column if the above makes sense :) -- adetorry ------------------------------------------------------------------------ adetorry's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29151 View this thread: http://www.excelforum.com/showthread.php?threadid=488723 See: http...

Formula for difference between numbers without + or-
I'm fairly new to Excel. I want to create a formula to show the difference between 2 numbers, in different columns, but without positive or negative values. eg. the difference between 1 and 7 is 6. The difference between 7 and 1 is 6. I do NOT want to see the result as -6 and 6. Imagine the cells are A3 and B3, for these numbers. How do I do this please? =ABS(A1-B1) -- Kind regards, Niek Otten Microsoft MVP - Excel "ParvenuJohn" <ParvenuJohn@discussions.microsoft.com> wrote in message news:9A117DC6-AF31-4288-BC74-C18734D0C4AE@microsoft.com... | I'm fairly n...

Rounding numbers in charts
HHi U'm not very good with computers. And I did a spreadsheet on excel and had percentages I wanted to put in a chart...when I created the chart (piechart) the values came out all inacurate. I wanted one to be 8% and it came out 6%. I heard you couold try and fix it using decimal places but that only makes a minor difference. Is there something I dont know about thats fairly simple to do? Hi Andrea If you'ld like to give us an example of your data (e.g. three categories and the associated percentages) and explain to us where the percentages came from - did you type th...

finding unique numbers
I have a column of numbers. Some numbers may appear in the column more than one time. I need to find out the numbers that appear only one time. For instance the column looks like this: 32 47 15 92 32 15 92 I need to find out that 47 is only used once. How can I do this? Thanks. Assuming that A1:A7 contains your data, try... B1, copied down: =IF(ROWS(\$B\$1:B1)<=SUM(--(COUNTIF(\$A\$1:\$A\$7,\$A\$1:\$A\$7)=1)),INDEX(\$A\$1:\$A\$ 7,SMALL(IF(COUNTIF(\$A\$1:\$A\$7,\$A\$1:\$A\$7)=1,ROW(\$A\$1:\$A\$7)-ROW(\$A\$1)+1),ROW S(\$B\$1:B1))),"") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this...