Rounding Up 1

[SomeYahoo]

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!

 

[bltseattle]

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.

 

[psafety]

GensetGuy,

If an equation helps:

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

 

[CajunCenturion]

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

 

[psafety]

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.

 

[electricpete]

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.

 

[Gryantaylor]

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)>>

 

[Gryantaylor]

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

 

[jghrist]

Gryantoylor,

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

 

[johnwm]

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:

http://www.essexsteam.co.uk

 

[EGT01]

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.....;-)

 

[iken]

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.

 

[johnwm]

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:

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:

http://www.essexsteam.co.uk

 

[SomeYahoo]

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?

 

[quark]

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.

 

[SomeYahoo]

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

Thanks for the help anyway!

 

[electricpete]

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.

 

[electricpete]

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.

 

[jghrist]

GenSetGuy wrote:

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.

 

[PEStructural]

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