How do I format cells to round up?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom - 03 Mar 2008 21:39 GMT

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.

Reply to this Message

CorporateQAinTX - 03 Mar 2008 21:50 GMT

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

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

Reply to this Message

Tom - 03 Mar 2008 22:05 GMT

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.

> 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
[quoted text clipped - 10 lines]
> > to the nearest integer. My skills here are limited so please reply in
> > "layman's" terms.

Reply to this Message

Dave Peterson - 03 Mar 2008 22:08 GMT

=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.
[quoted text clipped - 13 lines]
> > > to the nearest integer. My skills here are limited so please reply in
> > > "layman's" terms.

Signature

Dave Peterson

Reply to this Message

CorporateQAinTX - 03 Mar 2008 22:18 GMT

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.

Reply to this Message

Tom - 03 Mar 2008 22:28 GMT

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.

> 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
[quoted text clipped - 23 lines]
> > > > to the nearest integer. My skills here are limited so please reply in
> > > > "layman's" terms.

Reply to this Message

RagDyer - 03 Mar 2008 22:50 GMT

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.

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Thanks. I don't think I'll be able to write a function but I thought I
> might
[quoted text clipped - 42 lines]
>> > > > in
>> > > > "layman's" terms.

Reply to this Message