If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Adding Round funciton to handful of numbers
Hi,
I have a sheet filled with raw data. I need to round each number and was wondering if there is a formula or macro I can run to accomplish this. All the numbers were entered in by hand. Basically, I would like to highlight each number I need rounded and then run the formula/macro. Let me know if this is possible. Thanks |
#2
|
|||
|
|||
Adding Round funciton to handful of numbers
Do you want to retain the unrounded value in the cell and only see the rounded number? Select the cells with any combination of mouse use and Ctrl and/or Shift keys, then run this macro (having chosen which of the two lines should be uncommented-out):Sub blah() For Each cll In Selection.Cells 'cll.Value = Round(cll.Value, 2)'for actual rounding, OR: cll.NumberFormat = "0.00" 'to see rounded versions Next cll End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119276 |
#3
|
|||
|
|||
Adding Round funciton to handful of numbers
I think you will need a macro to do this. How did you want your numbers
rounded (round up, round down, round to nearest interval, Banker's Rounding, normal rounding, to a set number of decimal places possibly coupled with one of the previous methods, some other way)? -- Rick (MVP - Excel) "Jamie" wrote in message news Hi, I have a sheet filled with raw data. I need to round each number and was wondering if there is a formula or macro I can run to accomplish this. All the numbers were entered in by hand. Basically, I would like to highlight each number I need rounded and then run the formula/macro. Let me know if this is possible. Thanks |
#4
|
|||
|
|||
Adding Round funciton to handful of numbers
"Jamie" wrote:
Basically, I would like to highlight each number I need rounded and then run the formula/macro. If you want to use a macro, the following should suffice. Sub doit() Dim cell As Range For Each cell In Selection cell = WorksheetFunction.Round(cell, 2) cell.NumberFormat = "0.00" Next cell End Sub Some comments: 1. I added a line to change the format (cell.NumberFormat). Delete that if you wish to retain the original format. 2. I use Worksheet.Function.Round instead of the VBA Round function because the latter rounds different (so-called "banker's rounding"). For example, if the cell value is 295.425, WorksheetFunction.Round(cell,2) results in 295.43, whereas Round(cell,2) results in 295.42. 3. If you are unfamiliar with using macros, do the following: a. In an active worksheet, press alt+F11 to open the VB window. b. In the VB window, click on Insert Module. That should open the VB Editor pane. c. Copy and paste the macro text above into the VB Editor pane. d. In the worksheet window, select the cells to be converted, either one at a time or use ctrl+leftClick. Then press alt+F8, select the macro, and click Run. "Jamie" wrote in message news Hi, I have a sheet filled with raw data. I need to round each number and was wondering if there is a formula or macro I can run to accomplish this. All the numbers were entered in by hand. Basically, I would like to highlight each number I need rounded and then run the formula/macro. Let me know if this is possible. Thanks |
#5
|
|||
|
|||
Adding Round funciton to handful of numbers
"p45cal" wrote:
cll.Value = Round(cll.Value, 2)'for actual rounding Caveat emptor: VBA Round() performs "banker's rounding", which is different from Excel ROUND(). For example, with 295.425, VBA Round() results in 295.42, whereas Excel ROUND() results in 295.43. OR: cll.NumberFormat = "0.00" 'to see rounded versions If that is all the Jamie wants to do, I think it would be simpler to do it directly in the worksheet. Simply right-click, click Format Cells Number Number, and select the number of decimal places, which defaults to 2. ----- original message ----- "p45cal" wrote in message news Do you want to retain the unrounded value in the cell and only see the rounded number? Select the cells with any combination of mouse use and Ctrl and/or Shift keys, then run this macro (having chosen which of the two lines should be uncommented-out):Sub blah() For Each cll In Selection.Cells 'cll.Value = Round(cll.Value, 2)'for actual rounding, OR: cll.NumberFormat = "0.00" 'to see rounded versions Next cll End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119276 |
#6
|
|||
|
|||
Adding Round funciton to handful of numbers
This works but there are two changes I would like to make to it.
Can I have it so that the original number remains in the "formula bar" but the rounded number shows up in the cell? I would like to have it so that if the number were 88,888 the macro would insert the formula =Round("88,888", -2) Also, can the macro keep the same number formatting? The one you gave me changes the formatting to general. Thanks "JoeU2004" wrote: "Jamie" wrote: Basically, I would like to highlight each number I need rounded and then run the formula/macro. If you want to use a macro, the following should suffice. Sub doit() Dim cell As Range For Each cell In Selection cell = WorksheetFunction.Round(cell, 2) cell.NumberFormat = "0.00" Next cell End Sub Some comments: 1. I added a line to change the format (cell.NumberFormat). Delete that if you wish to retain the original format. 2. I use Worksheet.Function.Round instead of the VBA Round function because the latter rounds different (so-called "banker's rounding"). For example, if the cell value is 295.425, WorksheetFunction.Round(cell,2) results in 295.43, whereas Round(cell,2) results in 295.42. 3. If you are unfamiliar with using macros, do the following: a. In an active worksheet, press alt+F11 to open the VB window. b. In the VB window, click on Insert Module. That should open the VB Editor pane. c. Copy and paste the macro text above into the VB Editor pane. d. In the worksheet window, select the cells to be converted, either one at a time or use ctrl+leftClick. Then press alt+F8, select the macro, and click Run. "Jamie" wrote in message news Hi, I have a sheet filled with raw data. I need to round each number and was wondering if there is a formula or macro I can run to accomplish this. All the numbers were entered in by hand. Basically, I would like to highlight each number I need rounded and then run the formula/macro. Let me know if this is possible. Thanks |
#7
|
|||
|
|||
Adding Round funciton to handful of numbers
I would like the have my number normally rounded. For example, 88,888 would
round to 88,900. As if I were to have the following formula: =Round("88888",-2). "Rick Rothstein" wrote: I think you will need a macro to do this. How did you want your numbers rounded (round up, round down, round to nearest interval, Banker's Rounding, normal rounding, to a set number of decimal places possibly coupled with one of the previous methods, some other way)? -- Rick (MVP - Excel) "Jamie" wrote in message news Hi, I have a sheet filled with raw data. I need to round each number and was wondering if there is a formula or macro I can run to accomplish this. All the numbers were entered in by hand. Basically, I would like to highlight each number I need rounded and then run the formula/macro. Let me know if this is possible. Thanks |
#8
|
|||
|
|||
Adding Round funciton to handful of numbers
Yes, I would like to retain the unrounded value in the cell and only see the
rounded number? I'm not exactly sure what your macro is that you wrote below. Can you write it so that I can copy and paste it in a Module. What I would like to have is a cell that has 88,888 in it and after I run the macro the cell formula will be =Round("88888",-2) and the I will see the number 88,900. I would also like to make the macro so that I can highlight any cell either by hitting shift or holding down ctrl and the macro will apply to all selected cells. Thanks "p45cal" wrote: Do you want to retain the unrounded value in the cell and only see the rounded number? Select the cells with any combination of mouse use and Ctrl and/or Shift keys, then run this macro (having chosen which of the two lines should be uncommented-out):Sub blah() For Each cll In Selection.Cells 'cll.Value = Round(cll.Value, 2)'for actual rounding, OR: cll.NumberFormat = "0.00" 'to see rounded versions Next cll End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119276 |
#9
|
|||
|
|||
Adding Round funciton to handful of numbers
Okay, the fact that you want to retain the original number and only show the
rounded value is different than I originally interpreted your response. I'm thinking, as long as I understand what you want correctly, that you can just use normal cell formatting. Select all the cells you want to round (whether they current have values in them or not... think of the future possibilities), click Format/Cells in the menu bar, select the Number tab on the dialog box that comes up, select "Number" from the Category List and choose the number of decimal places you want all your numbers rounded to, then click OK. When you go back to your sheet, any numbers in those cells you selected originally will now display with the number of decimal places you picked, but the actual value in the cells will not be changed. -- Rick (MVP - Excel) "Jamie" wrote in message ... I would like the have my number normally rounded. For example, 88,888 would round to 88,900. As if I were to have the following formula: =Round("88888",-2). "Rick Rothstein" wrote: I think you will need a macro to do this. How did you want your numbers rounded (round up, round down, round to nearest interval, Banker's Rounding, normal rounding, to a set number of decimal places possibly coupled with one of the previous methods, some other way)? -- Rick (MVP - Excel) "Jamie" wrote in message news Hi, I have a sheet filled with raw data. I need to round each number and was wondering if there is a formula or macro I can run to accomplish this. All the numbers were entered in by hand. Basically, I would like to highlight each number I need rounded and then run the formula/macro. Let me know if this is possible. Thanks |
#10
|
|||
|
|||
Adding Round funciton to handful of numbers
While most people in Excel will just use WorksheetFunction.Round to avoid
the Banker's Rounding problem, there is a wholly VB solution... use the Format function. For whatever reason, Microsoft implemented Banker's in every VB function that rounds values (CInt, CLng, Mod, \ operator, etc.) *except* for the Format function. So, if you want to do "normal" rounding, use the Format function instead of the Round function... MsgBox Format(295.425, "0.00") -- Rick (MVP - Excel) "JoeU2004" wrote in message ... "p45cal" wrote: cll.Value = Round(cll.Value, 2)'for actual rounding Caveat emptor: VBA Round() performs "banker's rounding", which is different from Excel ROUND(). For example, with 295.425, VBA Round() results in 295.42, whereas Excel ROUND() results in 295.43. OR: cll.NumberFormat = "0.00" 'to see rounded versions If that is all the Jamie wants to do, I think it would be simpler to do it directly in the worksheet. Simply right-click, click Format Cells Number Number, and select the number of decimal places, which defaults to 2. ----- original message ----- "p45cal" wrote in message news Do you want to retain the unrounded value in the cell and only see the rounded number? Select the cells with any combination of mouse use and Ctrl and/or Shift keys, then run this macro (having chosen which of the two lines should be uncommented-out):Sub blah() For Each cll In Selection.Cells 'cll.Value = Round(cll.Value, 2)'for actual rounding, OR: cll.NumberFormat = "0.00" 'to see rounded versions Next cll End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119276 |
Thread Tools | |
Display Modes | |
|
|