×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Rounding Up

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!

RE: Rounding Up

From the menu DATA>VALIDATION you can set it so that only integers can be input.  If someone put in a decimal you can set it up to display a prompt message for the correct input.

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

GensetGuy,

If an equation helps:

= INT((24.1/1)+1)*1

RE: Rounding Up

The idea of using an equation makes sense, but be sure that you handle the case where the value is equal to an integer.

= 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

This has given good results on applications I've needed.

# = 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

If I'm understanding right the original poster wants to know if it can be done without equations (formatting, vba).

As far as I know, it can't.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Rounding Up

you can preform text functions on a numeric value. SO... search for the "." in the text string <<find(".",A1))>>, then take everything before that location in the text string minus one <<Left(A1,find(".",A1)-1)>> Finaly just add one <<Left(A1,find(".",A1)-1)+1>> and you'll have a simple formula that will round up. If you want to get carried away write the above formula into a custom function like <<=roundup(A1)>>

RE: Rounding Up

Never mind, I just reread the post. electricpete is right, I don't think you can format the cells to round up. But you can write your own roundup function :)

RE: Rounding Up

Gryantoylor,

Seems like a lot of trouble to replace the built-in Roundup function.

RE: Rounding Up

Make sure that you don't confuse actual rounding up (which will change the value stored in the cell) with changing the format of the cell which will change the display but not the underlying value.

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

Suppose you could enter the formula
=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

Why can't you just use the format function on the tools menu. This in effect decreases the decials places to zero, and rounds up the displayed value, but not the actual value.

RE: Rounding Up

If you actually want to change the stored value, rather than the displayed value then stick a macro in the Selection_Change event of the Worksheet:

CODE

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
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

(OP)
Thanks for all the tips!

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

But you will get a round up for decimals above 0.5 and rounddown if it is below 0.5. Just right click on a cell and go to Format Cells, click on Numbertab, select category as Number and make Decimal Places as 0.

The values remain same but cell contents show up as I said above.

RE: Rounding Up

(OP)
Oh... I thought iken knew a way to round all of them up.

Thanks for the help anyway!

RE: Rounding Up

If you go to format / cells / Number / Number, you can select the number of decimal places to round to but I just tried it and it performed normal rounding, not rounding up.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Rounding Up

Sorry guys I wrote my response before the last two (3 messages in 2 minutes... didn't type fast enough)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Rounding Up

GenSetGuy wrote:

Quote:

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?

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

GensetGuy,

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

(OP)
I have done a small abount of VB in the past, but it would take me a long time to get re-spun.  Thanks anyway PEStructural.

RE: Rounding Up

In case you were interested, this is the code that would be used:

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

Why don't you just hide the rounded output cells & display only the input. Or you can put the output in another worksheet. Protect your formulas.

RE: Rounding Up

I'm with lilliput1, just have an input column and a ROUNDED-UP column. The formula for rounding up cell A1 would be:
=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

(OP)
Thanks for all the input... I was just wondering if there was an easy way with formatting, etc.  I ended up just hiding cells.

RE: Rounding Up

To get the tool bar to show, right click anywhere on the toolbar (at the top). Tick the formating toolbar. This should then show.

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close