 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2008Rounding Off
$248,071 $260,290 $261,640 $106,316 $111,553 $112,131 $0 $0 $0 $354,387 $371,843 $373,771 I need to enter all of the above numbers into a form that looks like this: xx 0,000 So, I have to enter the first two didgits. I need to round off the rest. The first row would be: $250,000 $260,000 $260,000 How would I construct a formula to do that? Thanks If you enter the 2 digits in A1:Axxx, put this formula in B1 =A1*10000 and drag the formula down through Bxxx. Then format the cells as currency with 0 places of decimal. If you enter the entire number instead of the first 2 digits there are rounding methods that will accomplish what you want. Tyro > $248,071 $260,290 $261,640 > $106,316 $111,553 $112,131 [quoted text clipped - 14 lines] > > Thanks Tyro and I read your question differently: =ROUND(A1,-3) rounds to nearest thousand best wishes  SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email > $248,071 $260,290 $261,640 > $106,316 $111,553 $112,131 [quoted text clipped - 14 lines] > > Thanks Thanks, I think this solves it except my formula would be =Round(A1,-4) Is there a way I can insert this formula into a cell that already has a result from another formula? If I have 248,361 in a cell, which is the result of say, c16-c15, can I make the answer show up as 250,000 instead of 248,361 without having to input the formula =round(A1,-4) into yet a different cell? Thanks again. >Tyro and I read your question differently: >=ROUND(A1,-3) rounds to nearest thousand >best wishes How about a macro to add the ROUND to existing formulas? Adjust the ",2" to your liking......presumably ",-4" Sub RoundAdd() Dim myStr As String Dim cel As Range For Each cel In Selection If cel.HasFormula = True Then If Not cel.Formula Like "=ROUND(*" Then myStr = Right(cel.Formula, Len(cel.Formula) - 1) cel.Value = "=ROUND(" & myStr & ",2)" End If End If Next End Sub Gord Dibben MS Excel MVP >Thanks, I think this solves it except my formula would be >=Round(A1,-4) [quoted text clipped - 11 lines] >>=ROUND(A1,-3) rounds to nearest thousand >>best wishes No; but when you have copies this down the column do this: Select all the cells with the ROUND formula Use Copy With cells still selected; Edit | Paste Special ->Values Now the formulas are converted to number and you can delete the original range best wishes SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email > Thanks, I think this solves it except my formula would be > =Round(A1,-4) [quoted text clipped - 11 lines] >>=ROUND(A1,-3) rounds to nearest thousand >>best wishes You could add the round function to the already existing formulas right in the cell containing the original formulas. For example: =C16-C15 Change to: =Round(C16-C15,-4) SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Thanks, I think this solves it except my formula would be =Round(A1,-4) Is there a way I can insert this formula into a cell that already has a result from another formula? If I have 248,361 in a cell, which is the result of say, c16-c15, can I make the answer show up as 250,000 instead of 248,361 without having to input the formula =round(A1,-4) into yet a different cell? Thanks again. On Sat, 26 Jan 2008 12:43:37 -0400, "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote: >Tyro and I read your question differently: >=ROUND(A1,-3) rounds to nearest thousand >best wishes thanks, I figured that out finally after much trial and error. That is exactly what I wanted to do. Thanks for the macro, whoever posted that too. IT was also useful. >You could add the round function to the already existing formulas right in >the cell containing the original formulas. [quoted text clipped - 3 lines] >Change to: >=Round(C16-C15,-4) Appreciate the feed-back. SignatureRegards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- thanks, I figured that out finally after much trial and error. That is exactly what I wanted to do. Thanks for the macro, whoever posted that too. IT was also useful. On Sat, 26 Jan 2008 11:01:05 -0800, "RagDyeR" <ragdyer@cutoutmsn.com> wrote: >You could add the round function to the already existing formulas right in >the cell containing the original formulas. [quoted text clipped - 3 lines] >Change to: >=Round(C16-C15,-4) Exactly what numbers are you entering? 248071 or 24? You say you enter the first 2 digits. Are you really entering the entire number? And what are your rounding rules? > $248,071 $260,290 $261,640 > $106,316 $111,553 $112,131 [quoted text clipped - 14 lines] > > Thanks I already have the entire numbers, which are the result of formulas in cells. I'm rounding to xx0,000. So I guess that would be to the nearest 10,000? Thanks for the help >Exactly what numbers are you entering? 248071 or 24? You say you enter the >first 2 digits. Are you really entering the entire number? [quoted text clipped - 18 lines] >> >> Thanks =ROUND(A1,-3) rounds to nearest thousand best wishes SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email >I already have the entire numbers, which are the result of formulas in > cells. I'm rounding to xx0,000. So I guess that would be to the [quoted text clipped - 25 lines] >>> >>> Thanks =ROUND(A1,-4) rounds to nearest ten-thousand best wishes SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email >I already have the entire numbers, which are the result of formulas in > cells. I'm rounding to xx0,000. So I guess that would be to the [quoted text clipped - 25 lines] >>> >>> Thanks |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.