Rounding Up
Rounding Up
(OP)
Is there a way (conditional formatting perhaps?) to automatically round up an input decimal number to the next integer (i.e. 24.1 automatically becomes 25)? I know you can do this with the ROUNDUP function, but you would then have to hide the actual input cell.
Just wondering if there was a shortcut. Thanks!
Just wondering if there was a shortcut. Thanks!





RE: Rounding Up
As far as I can tell, otherwise you can not automatically round up a value unless you would write a macro that runs when values are input. I would have to look pretty hard at why ROUNDUP would not work before going that route, and that approach would also provide a "backup" of what was entered over what was carried forward in further computations.
RE: Rounding Up
If an equation helps:
= INT((24.1/1)+1)*1
RE: Rounding Up
= INT((24.0 / 1) + 1) * 1
may not yield the desired result.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
RE: Rounding Up
# = the number you want to round
RN = the number to round to
Rounded Up Number = INT((#/RN)+0.5) * RN
Rounded Down Number = INT((#/RN)-0.5) * RN
Take your number #, and divide it by what you want to round to, RN. Add or subtract 0.5 to it, and take the integer part. Then multiply by the RN number again.
Found on MrExcel forum a couple of years ago. And there very well may be exceptions where it doesn't work as well as other methods.
RE: Rounding Up
As far as I know, it can't.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Rounding Up
RE: Rounding Up
RE: Rounding Up
Seems like a lot of trouble to replace the built-in Roundup function.
RE: Rounding Up
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk
RE: Rounding Up
=ROUNDUP(24.1,0)
in the formula bar and hit the F9 button. This evaluates the formula and displays only the result in the cell. But I think I would just enter the number 25.
In the year 2525.....
RE: Rounding Up
RE: Rounding Up
CODE
Set t = Range("A8") 'or your cell to alter
If t.Value > Int(t.Value) Then
t.Value = Int(t.Value) + 1
End If
End Sub
You will need to tweek that depending on how you want to cover non-numeric or negative inputs.
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk
RE: Rounding Up
I would like to retain the input number (for reference later), but display/print the rounded number. I am interested in iken's solution, but don't seem to have that option under my tools menu. Any insight?
RE: Rounding Up
The values remain same but cell contents show up as I said above.
RE: Rounding Up
Thanks for the help anyway!
RE: Rounding Up
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Rounding Up
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Rounding Up
If you want to retain the original number, it's best to make the rounded number in a different cell with the ROUNDUP function, at least if you're going to do anything with the numbers. If you do figure out a way to format the number rounded up while retaining the actual number, you'll have to continually explain to users why 2+2=3 when the cells really contain 1.2+1.1=2.3.
RE: Rounding Up
How familiar are you with VBA? I have a procedure in mind that could automatically copy your input number, say 3.47, to a different cell and then roundup your original number. There is a VBA procedure that can be programmed that as soon as you hit enter after inputting the number, it would perform the macro automatically without you needing to press any buttons or anything.
If you feel comfortable with VBA, let me know and I'll post my thoughts.
PEStructural
RE: Rounding Up
RE: Rounding Up
Sub auto_open()
' Run the macro DidCellsChange any time a entry is made in a
' cell in Sheet1.
ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange"
End Sub
Sub DidCellsChange()
Dim KeyCells As String
' Define which cells should trigger the KeyCellsChanged macro.
KeyCells = "E11"
' If the Activecell is one of the key cells, call the
' KeyCellsChanged macro.
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then KeyCellsChanged
End Sub
Sub KeyCellsChanged()
'
Application.ScreenUpdating = False 'Turning off screen
'
' Copying original value
Range("E11").Select
Selection.Copy
' Pasting original value
Range("G11").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
' Rounding up original value
Range("E11") = Application.WorksheetFunction.RoundUp(Worksheets("Sheet1").Range("E11"), 0)
Range("E11").Select
'
Application.ScreenUpdating = True
'
End Sub
You would just have to change the E11 and G11 to whatever your cells are. To copy this into Excel VB, go to Tools>Macro>Visual Basic Editor. On the left side of the screen you'll see a box named Project - VBAProject. In that box you'll see something that says VBAProject (Filename.xls). The filename.xls would be the actual file you're using, not "filename". Then you right click on VBAProject (Filename.xls), then click Insert>Module. Then in the module, copy the above code into it and hit the save button on the top of the screen. Then exit the Visual Basic Editor.
Save your worksheet and exit Excel. Open the spreadsheet back up and click Enable Macros. Now if you input a number into E11 (or whatever you changed it to), it will copy the original input number to G11 and roundup the value in E11.
Hope this helps,
PEStructural
RE: Rounding Up
RE: Rounding Up
=CEILING(A1,1)
Now just show/hide the columns at will. If you need more visual clues then you could have the rounded up column with larger font, different color font, or highlight the cells, or etc...
Ken
RE: Rounding Up
RE: Rounding Up
The button I mentioned has two lines. The top with an arrow next to a dot and one 0, the second with an arrow next to a dot and two 0's.
One mirrored for increasing decimal places.