LIMITING CELLS TO 2 DECIMAL PLACES IN EXCEL

I have a small issue with an excel list that I am pulling information
from. Its a basic price list pulled from another companys list. The
problem is the company that the price originate from use an equasion
from another list to derive the actual totals. this means that numbers
can be up to 5 decimal places long. I need to know how to be able to
reduce absolutely, the entire column of numbers to 2 decimal places.
Not so that it just looks like 2 (as in making it a currency) but
actually rounding the real numbers to 2 dec places. Is there a simple
way to do this or is it more involved?

0
cvrjcu (3)
2/10/2006 2:26:58 AM
excel 39879 articles. 2 followers. Follow

4 Replies
439 Views

Similar Articles

[PageSpeed] 22

=ROUND(a1,2)
That will round to the nearest 2 decimal places.
After you use this formula, you'll probably want to copy the results, keep 
your selection in place, then hit Edit-->Paste special-->Values.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com
Check out the NEWsgroup stats!
Check out: www.ExcelUserConference.com

<cvrjcu@gmail.com> wrote in message 
news:1139538417.998389.281060@g47g2000cwa.googlegroups.com...
>I have a small issue with an excel list that I am pulling information
> from. Its a basic price list pulled from another companys list. The
> problem is the company that the price originate from use an equasion
> from another list to derive the actual totals. this means that numbers
> can be up to 5 decimal places long. I need to know how to be able to
> reduce absolutely, the entire column of numbers to 2 decimal places.
> Not so that it just looks like 2 (as in making it a currency) but
> actually rounding the real numbers to 2 dec places. Is there a simple
> way to do this or is it more involved?
> 


0
ng1 (1444)
2/10/2006 2:33:52 AM
thanks for the fast reply Anne, however I cant seem to get that
working. Can you explain how I would get an entire column to do that?
Im a bit of a newb to excel functions.

0
cvrjcu (3)
2/10/2006 3:04:35 AM
thanks for the fast reply Anne, however I cant seem to get that
working. Can you explain how I would get an entire column to do that?
Im a bit of a newb to excel functions.

0
cvrjcu (3)
2/10/2006 3:04:45 AM
In a column adjacent to your data, enter the formula

=ROUND(A1,2)

where A1 is the first data cell. Then, copy that formula down as 
far as you need to go. To replace the original data, copy the 
cells with the formulas, go to the Edit menu, choose Paste 
Special, and choose the Values option.


-- 
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



<cvrjcu@gmail.com> wrote in message 
news:1139540685.137494.170950@g44g2000cwa.googlegroups.com...
> thanks for the fast reply Anne, however I cant seem to get that
> working. Can you explain how I would get an entire column to do 
> that?
> Im a bit of a newb to excel functions.
> 


0
chip1 (1821)
2/10/2006 3:11:45 AM
Reply:

Similar Artilces:

Stripping namespace #2
Given an XmlNode with namespace (NamespaceURI has a value), I want to be able to make a clone of it (XmlNode.CloneNode) but without the namespace. How can I strip the namespace from a node? Clive Dixon wrote: > Given an XmlNode with namespace (NamespaceURI has a value), I want to be > able to make a clone of it (XmlNode.CloneNode) but without the namespace. > How can I strip the namespace from a node? If the namespace is different then the new node is a not a clone of the old node. What you can do is create a new node in a different namespace respectively in no namespace: ...

cell color problems
After changing my Windows color scheme, I discovered that all the cells in one of my spreadsheet were the background color. I wanted to restore them to a white background. Strangely enough, only one existing spreadsheet is effected; new spreadsheets have the default white color. I tried all Format Cells options to restore a white color. This worked ok, but I could no longer see gridlines displayed even though Tools / Options / View tab / Gridlines box is checked. Finally, I used Format / Auto Fomrat / None. This restored my gridlines but to my amazement, my spreadsheet ballooned from 500...

Pivot table error when Opening an Xml Spreadsheet from MS excel Xp
Hi, I have saved an excel file as an xml file that has a pivot table. When i open the same xml spreadsheet again in excel and click on the pivot table, it gives me the error as below. "This pivot table report is not valid" But it works when i refresh data for the pivot table. Hence, I have to refresh the data every time i open the xml spreadsheet to make the pivot table working. Is there any way to make the pivot table working without refreshing the data? Please advise a forum to which i can submit the above question. Thanks and Regards, Jagadeesan ...

RMS 2.0 License handling
hi Microsoft, I am not sure how you handle the licenseing for the RMS 2.0 the way I saw it works is that you can install the RMS on machine and then activate the license on that machine. this will allow you to use other machine depends on the license key. The problem here is that what if something goes wrong to machine then you can not use the same license key again go back and fort with MS and request for other key. The way it should work is that you should have some provision in RMS that you can deactivate the license and then allow it to activate it on any other machine you like. I...

Protection problem #2
Hope someone can help. I have a shared workbook with a summary sheet which consolidates all other sheets. I set this up and it all worked nicely.However I am now getting an error when trying to consolidate data. [The cell or chart you are trying to change is protected and therefor read only]. When I go to unprotect the sheet the option to 'unprotect sheet' is greyed out. The other worksheets have the option to 'protect sheet' greyed out. Anyone have any ideas why this is. I could rebuild the summary sheet, no big job but it is frustrating. Thanks Russ Hi changing the p...

File Size Limit??
Hi, Can anyone tell me if there is a limit to the file size of an Excel 2000 document. We've got a guy here who has a 63Mb spreadsheet, and keeps wondering why he's getting "Out of memory" messages every time he opens it. I've put an extra 512Mb in his PC, but he's still getting lock-ups. He's convinced that there are no limits to the size of the file, but I'm pretty certain that there is... Anyone help? Thanks, Jon. The limit is memory. -- Don Guillett SalesAid Software donaldb@281.com "Jon" <anonymous@discussions.microsoft.com> ...

locking / unlocking cell in formula
E F 1 Do you own a guitar validation cell (yes/no) 2 If yes is it a Gibson 3 Is it a 5 string 4 Is it electric 5 Do you own a piano 6 Is it electric 7 Does it have 88 keys 8 Do you own a TV 9 Is it color 10 Is it an LCD 11 Is it 25" 12 Is it 32" 13 Is it 42" 14 Do you own a radio I would like to lock cells F2:F4 if F1 contains the word "no" I would also like to lock cells F6:F7 if F5 contains the word "no" I would also like to lock ce...

Get column of named cell
I have a named cell - StaffHeader1 - I want to refernce just the Column property of this cell in another piece of vba - how do I do that ? So in my code I am going to do this; iColumn = Column(StaffHeader1) Sheet6.Cells(3, iColumn + 1).Value = iStaffName Any help appreciated Thanks Check your other post. Isis wrote: > > I have a named cell - StaffHeader1 - I want to refernce just the Column > property of this cell in another piece of vba - how do I do that ? > > So in my code I am going to do this; > iColumn = Column(StaffHeader1) > Sheet6.Cells(...

Limits
I am doing a fairly easy programme on excel. It is a catchment runoff, and evaporation calculator. But i would like to place a limit on a cell, and have that exess go into another column, is this possible? EG 75 rainfall, soil can hold only 10mm more. SO 10mm INFILTRATES, ie that cell should have a limit of the max soil moisutre, the other 65mm RUNOFF's and thats a different column. ANYBODY PLEASEEEEEEEEEE!!!! lol You need a Workbook_Change event macro for that. The code you would write in that macro should first check that the change occurred in a cell within the range you want. ...

Excel locks for no reason
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C3DC0B.6B1B0630 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am using WinXP Home and Office Professional 2002, as I have been = since 2002. Recently, Excel started doing something wierd. When I open = my favorite spreadsheet (the one I use for all my financial stuff), I = frequently get a dialog that says that the file is locked for editing = and I can only open it as read-only. It seems to think that another = instance of the spreadsheet is in use when i...

Record Sharing #2
Is there anyway to globally delete the sharing information for records (i.e. accounts, contacts) that occurs after a record is reassigned? We are having issues while online where our regional sales people are seeing records that they do not own. We can manually delete the shares, but with thousands of records, we would like something more automated. Further, is there a way to turn off the automatic share creation when a record is reassigned? We are running CRM 3.0. If there isn't something built withing the application, is there a way via SDK? Thanks in advance, Tim In the or...

Preserving cell reference after a sort
How do I preserve a reference to data when the data's cell is moved by a sort? Example: F5 refers to M10 (either =M10 or =$M$10) and M10 contains "Domino's", then column M is sorted and "Domino's" is moved to M15, and I want F5 now to contain =M15 (and hence "Domino's") automatically, like when a new column is inserted. -Scott The short answer is that you can't. It's like if M10 contained "Domino's" and then you copied another cell into M10 so that M10 now contains "Four Brothers", there's no way F5 can sti...

Pivot Table From Different Sources #2
Hi All, I am trying to build a new pivot table from two sources, one of them is an existing pivot table which has an external link to Oracle db, the other source is a normal spreadsheet, unfortunately, it's not working. Any one has an idea? appreciate your help. -- Excel Power User ...

Harvesting URL data from cells
I have a list of 200 or so cells which are each hyperlinked to individual web pages. I would like to generate a list of the actual hyperlink addresses rather than the visible cell content. The only way I seem to be able to do it is to open each page individually and then cut 'n paste the URL? Thanks You can employ a User Defined Function. Function HyperlinkAddress(cell) As String If cell.Hyperlinks.Count > 0 Then _ HyperlinkAddress = cell.Hyperlinks(1).Address End Function Usage is: =HyperlinkAddress(cellref) If not familiar with VBA and macros, see David McRitchie...

How to insert small colored dots or rectangles in cells of excel
I am trying to insert colored dots or rectangles in cells of excel. How to do this? -- George You could select the cell and then Insert|Symbol and select Webdings or Wingdings etc. and then format the cell font color to whatever you want. "George A. Yorks" wrote: > I am trying to insert colored dots or rectangles in cells of excel. How to > do this? > -- > George ...

adding numbers #2
Hello, I have a spreadsheet where I am storing hours. So 1h or 5h, etc... Is there a way I can select a bunch of cells. and add these numbers. Even though there is "h" in the cell? thanks SUM works just as well on time, just format the result cell as [h]:mm if it might exceed 24 hours. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "greg" <iuouh@ghfhg.com> wrote in message news:OvM5oBU1IHA.1240@TK2MSFTNGP02.phx.gbl... > Hello, > I have a spreadsheet where I am storing hours. > So 1h or 5h, etc... > I...

Cell Selection?
I have a colum of numbers 198 0 0 198 2 What I need, is to be able to select 3. the 198's are not going to be used in the next part of my equation. That seems simple enough, however all the numbers could be usable (not 198) and I need to use just the first three. Any ideas? One interp / way, using non-array formulas Assuming source numbers in A1 down Put In B1: =IF(COUNT($C$1:C1)>3,"",C1) In C1: =IF(ISERROR(SMALL(D:D,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0))) In D1: =IF(A1="","",IF(A1=198,"",ROW())) Select B1:D...

2008 R2 dpm 2007 setup: SIS-Limited not found?
I'm trying to run the command line to install the SIS component via ocsetup.exe SIS-Limited /quiet /norestart but it tells me it cant find this SIS-Limited component (on 2008 r2 x64).. Any ideas what i need to do to get past this? Thanks It turns out you just need to add the file services role in r2, dont need the manual command.. "markm75g" wrote: > I'm trying to run the command line to install the SIS component via > ocsetup.exe SIS-Limited /quiet /norestart > > but it tells me it cant find this SIS-Limited component (on 2008 r2 x64).. ...

Help! IF function is too limited
Hi! I've created a drop-down list of cities in a cell, under which there are two more cells to be filled out with the address and zip codes corresponding to each of the cities. How can I do this, knowing that the original list is a three-column list made of city-address-zip? -- Ringo ------------------------------------------------------------------------ Ringo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27502 View this thread: http://www.excelforum.com/showthread.php?threadid=470178 Use VLOOKUP into the original; list using the DV value =VLOOKUP(B...

Automatic undesirable cell filling
I have a file with 5 charts. In one of these charts, all of suden colors became crazy. All I had formated, desapeared. Also,when I digi a figure or anything, the cell automatically is filled with the gree color. There is no use on trying to remove it or fill with anothe color. It will still be green. What to do -- Message posted from http://www.ExcelForum.com Someone may have added conditional formatting to the cells. To remove it: Select the cells Choose Format>Conditional Formatting Click the Delete button Add a check mark to all three conditions Click OK, click OK Ivan Santiago <...

Checking for a blank Cell #3
Hi Julie managed to fix the #REF problem. However, it is still doing th same (i.e. displaying EXPIRED instead of N/A hwn there is no data in th corresponding cells? Hope you can help? Thanks a lot! Mar -- stevie_ra ----------------------------------------------------------------------- stevie_ray's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1067 View this thread: http://www.excelforum.com/showthread.php?threadid=27416 =if(iserror(yourformula),"EXPIRED",yourformula) should work ok. stevie_ray wrote: > > Hi Julie managed to fix the #REF ...

Q: Rules limit
There is a limit for Exchange Rules on a 32K-size (KB147298) Whether there is a way to change this limit for single users? For me already three users have addressed with such requests :- Thanks Alexander Kenin This is not a configurable limit. It's limited by the size of one RPC packet. "Kenin Alexander" <anonymous@discussions.microsoft.com> wrote in message news:80981636-20EB-4C64-B514-F9BCA635AFE7@microsoft.com... > There is a limit for Exchange Rules on a 32K-size (KB147298). > Whether there is a way to change this limit for single users? > For me already thre...

authentication #2
can i used only PKI to authenticate. and disable otherways to authenticate for email. on user account level. ...

drop down calendar in excel cell
Is there a way to have a drop down calendar in an excel field? CG CG, There's an ActiveX calendar. Insert, Object, Calendar Control 10.0. If you want to show/change a date in a cell, it has a Linked Cell property you can point to the cell. It isn't a pop-up -- I think you'll need macro code to make that happen. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "CG" <cgs74@sbcglobal.net> wrote in message news:OskN7OGlEHA.712@TK2MSFTNGP09.phx.gbl... > Is there a way to have a drop down calendar in ...

Filters #2
Help! I've tried every filter in Outlooks customize view facility and I still cannot produce a report that: Has all appointments from today forward. I filter "start" "on or after" "today" (or I use the date) but everytime I do - I get all recurring appointments from previous years. I need those items for this year, but not for 2001-2002 & 2003. Is there any way to filter these out? ...