Round up entire worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to make it so every number entered in a worksheet is
automatically rounded up? My work wants the numbers rounded up (12.01
rounded up to 13.00), however they want the actually amount to be typed in
for possible future audits.

I don't have to do this cell by cell do I?
 
The only way to do that is to use 2 cells. One for the actual amount and one
for the rounded amount.

A1 = 12.01

B1 FORMULA: =CEILING(A1,1)

Biff
 
Quick and easy way to do it - create the sheet as you normally would, using
actual values. Format, etc, per usual. Then create a copy of the sheet in
the same workbook (Right click the sheet tab and choose Move or copy-make
sure to click the Create a Copy check box) and in the new sheet, replace the
values with a reference to the cells from the original sheet using the
ceiling function. Then you have everything the bosses want without double
the work.
If your sheets were named Actual and Rounded and cell B3 in the actual sheet
contained a value, then the formula in cell B3 in the Rounded sheet would be:
=Ceiling(Actual!B3,1) - once you have the first, you can use AutoFill to
copy to adjacent cells.
Good luck!
 
Hi!
Thank you for the info. Very helpful. One question - what does the number
after the comma stand for in the formula Ex. =ceiling(A1,1)?
 
what does the number after the comma stand for in the
formula Ex. =ceiling(A1,1)?

That means to roundup to the next increment of 1.

12.01 rounds up to 13

A1 = 33

=CEILING(A1,10)

Rounds up to the next increment of 10. Result = 40

=CEILING(A1,5)

Rounds up to the next increment of 5. Result = 35

Biff
 
If you have questions about Excel functions, it's (almost) always worth
looking in Excel help. As well as an explanation, it will usually give
examples, and also related functions. As far as I am aware, DATEDIF is the
only function not included in help.
 
The workbook is full of various sheets that are compiled from many
organizations...and go to the state. I cannot create additional sheets.

I did look in help-and it did not tell me how to round an entire worksheet
up, that's why I came here. I just want to type a number in, like 123.49 and
have it automatically jump up to 124.

I take it there is no way to do this? Using the currency option works,
except it rounds down if it's .49 or less :( I want all numbers to round up
to the next whole dollar.

Thank you.
 
You can use the following code to add the ceiling function to range of values:

Sub RndUpRng()
Dim ColumnCount As Integer
Dim RowCount As Integer
For RowCount = 1 To Selection.Rows.Count
For ColumnCount = 1 To Selection.Columns.Count
ActiveCell = "=Ceiling(" & (ActiveCell) & ",1)"
If ColumnCount < Selection.Columns.Count Then
ActiveCell.Offset(0, 1).Activate
Else
ActiveCell.Offset(1, -Selection.Columns.Count + 1).Activate
End If
Next ColumnCount
Next RowCount
End Sub

Select only cells that contain values, not formulas, or the formulas will be
converted to their current value. If you don't know how to enter or use
code, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro
instructions by David McRitchie (Microsoft MVP)
 
To Boni,
Your code works great to round multiple rows and columns. However, I am trying to add code to then copy > paste special > values only to the code and have not been successful. I have tried adding the lines of code under the rounding function and have tried a separate subroutine. Neither works. Can you help me make this work? Here's the code as edited:

Dim ColumnCount As Integer
Dim RowCount As Integer
For RowCount = 1 To Selection.Rows.Count
For ColumnCount = 1 To Selection.Columns.Count
ActiveCell = "=Round(" & (ActiveCell) & ",0)"

' ADDED CODE_________________________________
ActiveCell.Copy
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
' END ADDED CODE____________________________________________

If ColumnCount < Selection.Columns.Count Then
ActiveCell.Offset(0, 1).Activate
Else
ActiveCell.Offset(1, -Selection.Columns.Count + 1).Activate
End If
Next ColumnCount
Next RowCount


For RowCount = 1 To Selection.Rows.Count
For ColumnCount = 1 To Selection.Columns.Count
'ActiveCell = "=Round(" & (ActiveCell) & ",0)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
If ColumnCount < Selection.Columns.Count Then
ActiveCell.Offset(0, 1).Activate
Else
ActiveCell.Offset(1, -Selection.Columns.Count + 1).Activate
End If
Next ColumnCount
Next RowCount



Boni wrote:

You can use the following code to add the ceiling function to range of
25-Apr-07

You can use the following code to add the ceiling function to range of values

Sub RndUpRng(
Dim ColumnCount As Intege
Dim RowCount As Intege
For RowCount = 1 To Selection.Rows.Coun
For ColumnCount = 1 To Selection.Columns.Coun
ActiveCell = "=Ceiling(" & (ActiveCell) & ",1)
If ColumnCount < Selection.Columns.Count The
ActiveCell.Offset(0, 1).Activat
Els
ActiveCell.Offset(1, -Selection.Columns.Count + 1).Activat
End I
Next ColumnCoun
Next RowCoun
End Su

Select only cells that contain values, not formulas, or the formulas will be
converted to their current value. If you don't know how to enter or use
code, see
http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacr
instructions by David McRitchie (Microsoft MVP

:

Previous Posts In This Thread:

Round up entire worksheet
Is there a way to make it so every number entered in a worksheet is
automatically rounded up? My work wants the numbers rounded up (12.01
rounded up to 13.00), however they want the actually amount to be typed in
for possible future audits.

I don't have to do this cell by cell do I?

The only way to do that is to use 2 cells.
The only way to do that is to use 2 cells. One for the actual amount and on
for the rounded amount

A1 = 12.0

B1 FORMULA: =CEILING(A1,1

Biff

Quick and easy way to do it - create the sheet as you normally would, using
Quick and easy way to do it - create the sheet as you normally would, using
actual values. Format, etc, per usual. Then create a copy of the sheet in
the same workbook (Right click the sheet tab and choose Move or copy-make
sure to click the Create a Copy check box) and in the new sheet, replace the
values with a reference to the cells from the original sheet using the
ceiling function. Then you have everything the bosses want without double
the work
If your sheets were named Actual and Rounded and cell B3 in the actual sheet
contained a value, then the formula in cell B3 in the Rounded sheet would be
=Ceiling(Actual!B3,1) - once you have the first, you can use AutoFill to
copy to adjacent cells
Good luck

:

Hi!
Hi!
Thank you for the info. Very helpful. One question - what does the number
after the comma stand for in the formula Ex. =ceiling(A1,1)?
--
Nancy :)


:

That means to roundup to the next increment of 1.12.
That means to roundup to the next increment of 1.

12.01 rounds up to 13

A1 = 33

=CEILING(A1,10)

Rounds up to the next increment of 10. Result = 40

=CEILING(A1,5)

Rounds up to the next increment of 5. Result = 35

Biff


If you have questions about Excel functions, it's (almost) always worth
If you have questions about Excel functions, it's (almost) always worth
looking in Excel help. As well as an explanation, it will usually give
examples, and also related functions. As far as I am aware, DATEDIF is the
only function not included in help.
--
David Biddulph


The workbook is full of various sheets that are compiled from many
The workbook is full of various sheets that are compiled from many
organizations...and go to the state. I cannot create additional sheets.

I did look in help-and it did not tell me how to round an entire worksheet
up, that's why I came here. I just want to type a number in, like 123.49 and
have it automatically jump up to 124.

I take it there is no way to do this? Using the currency option works,
except it rounds down if it's .49 or less :( I want all numbers to round up
to the next whole dollar.

Thank you.

:

You can use the following code to add the ceiling function to range of
You can use the following code to add the ceiling function to range of values:

Sub RndUpRng()
Dim ColumnCount As Integer
Dim RowCount As Integer
For RowCount = 1 To Selection.Rows.Count
For ColumnCount = 1 To Selection.Columns.Count
ActiveCell = "=Ceiling(" & (ActiveCell) & ",1)"
If ColumnCount < Selection.Columns.Count Then
ActiveCell.Offset(0, 1).Activate
Else
ActiveCell.Offset(1, -Selection.Columns.Count + 1).Activate
End If
Next ColumnCount
Next RowCount
End Sub

Select only cells that contain values, not formulas, or the formulas will be
converted to their current value. If you don't know how to enter or use
code, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro
instructions by David McRitchie (Microsoft MVP)

:

EggHeadCafe - Software Developer Portal of Choice
Custom Cached Server-side XML Scrolling News
http://www.eggheadcafe.com/tutorial...a6-8b3ce1c44d3e/custom-cached-serverside.aspx
 
Lovely solution, easy fix tro 2,271,269 cells that were begging to be rounded before being transferred to Oracle 10g! :) Thanks mate!
 
Back
Top