Rounding in excel

M

Mike W

How can I Round a large amount of #s w/o typing round formula in each cell

Ex: 39.548759631 to 40
 
V

VBA Noob

You could use the decrease decimal button on the formatting tool bar.

or format cells as Number with no decimal points.

However this is only a visual solution. Best to add another column an
enter =ROUND(A1,0) then paste special results

VBA Noo
 
C

CLR

Highlight the cells, then right-click > FormatCells > NumberTab > Number >
select zero decimal places > ok

Vaya con Dios,
Chuck, CABGx3
 
M

Mike W

Actually what I am looking for is for the detail of the cell to be rounded as
well. For example:

a1 = 12.356487

Visually it shows 12

I want to be able to have the detail of the cell equal to 12. So that when
I am using this cell in a calculation it views it as a whole number. The
only way I kow of is to retype the number as 12 or use the round function but
I am dealing with 100's of cells. Thanks.
 
R

Ragdyer

Try this:

<Tools> <Options> <Calculations> tab,
And *check* "Precision As Displayed".

Check first to make sure that this doesn't hurt any other calculations in
the rest of the WB.
 
J

Jerry W. Lewis

You could use the menu item Tools|Options|Calculation and check “Precision as
displayedâ€. Note that this will impact all calculations, not just the
particular ones that you intend; consequently it may have undesirable side
effects.

Jerry
 
C

CLR

Hi Mike......
This is kinda crude, but perhaps it's what you're looking for..........

Sub RoundMe()
Dim lastrow As Long, r As Long
Dim num1 As String
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For r = lastrow To 1 Step -1
If Cells(r, "A") > 0 Then
Cells(r, "A").Select
num1 = Selection.Value
End If
With ActiveCell
.Value = "=round(" & num1 & ",0)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
Next r
End Sub

Vaya con Dios,
Chuck, CABGx3
 
Top