How do I format cells to round up?

[Tom]

I want to format a cell or column in a worksheet to always round up the value
to the nearest integer. My skills here are limited so please reply in
"layman's" terms.

 

[CorporateQAinTX]

Have you tried using the =ROUNDUP() function? You're supposed to put the
number and then the number of decimal places in the parenthesis, but you can
list a cell value for number like A1 instead. If you want a whole integer
just put a 0 like this:

=ROUNDUP(A1,0)

This will take whatever number is in A1 and round it up every time.

QA Techie

 

[Tom]

I did that but it keeps giving me the circular reference error. The values
that appear in the cell are being entered as the result of a formula.

 

[Dave Peterson]

=roundup(yourexistingformulahere,0)

I did that but it keeps giving me the circular reference error. The values
that appear in the cell are being entered as the result of a formula.

 

[CorporateQAinTX]

If you want to set each cell up without any outside functions, I think you'll
need to try some VBA codes or macros. I'm not that proficient with them to
even know that that would work. Any time you write a function that refers to
the cell it's in you're going to get a circular error. My suggestion would be
to put the formula into the cell right of an input cell. Then go to Format
Cells < Security and lock it. When you've completed the sheet as far as you
want it, you can protect the sheet (Tools<Protect Sheet) and the formula
can't be erased. When you protect it, uncheck everything listed except choose
unlocked cells. Other than that the default is to round normally.

 

[Tom]

Thanks. I don't think I'll be able to write a function but I thought I might
be able to use the Format<Cells<Number<Custom route to get it done. Doesn't
sound like I can do that either.

 

[RagDyer]

Have you read Dave's suggestion?

Perhaps you didn't understand it!

You take your existing formula, the one that generates the number, and wrap
it in the Roundup() function.

Say your formula is in A1, and it looks like this:

=Sum(C1:C10)

You revise it to this:

=Roundup(Sum(C1:C10),0)

So, if you can devise a formula, you can easily revise it, to incorporate
the Roundup() function.