How do I stop a calculation rounding up

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lynneth - 13 Jan 2006 11:27 GMT

How do I stop a calculation rounding up or down without extending the
decimal points.

I have a client who is having problems with her invoices, as it
sometimes shows a penny too much, as it has rounded up. I realise that
we no longer work in half pennies, so is it possible to show the correct
figure

Thanks
Lynne

Signature

Lynneth

Reply to this Message

JE McGimpsey - 13 Jan 2006 12:08 GMT

See

http://www.mcgimpsey.com/excel/pennyoff.html

> How do I stop a calculation rounding up or down without extending the
> decimal points.
[quoted text clipped - 6 lines]
> Thanks
> Lynne

Reply to this Message

Selvarathinam - 13 Jan 2006 13:10 GMT

Hi Lynneth,

Use the below syntax for rounding off

=ROUND("Formula",2)

Put 2 if u to round it with 2 decimals or put 0 to roundoff it without
any decimals & format the cell for the decimals.

Regards,
Selvarathinam.
-----------------

> How do I stop a calculation rounding up or down without extending the
> decimal points.
[quoted text clipped - 6 lines]
> Thanks
> Lynne

Reply to this Message

JE McGimpsey - 13 Jan 2006 14:28 GMT

But note that while ROUND() may be useful, it doesn't get at the root of
the problem:

A1: 1
A2: =ROUND(A1/3,2)
A3: =ROUND(A1/3,2)
A4: =ROUND(A1/3,2)
A5: =SUM(A2:A4) ===> 0.99

> Use the below syntax for rounding off
>
> =ROUND("Formula",2)
>
> Put 2 if u to round it with 2 decimals or put 0 to roundoff it without
> any decimals & format the cell for the decimals.

Reply to this Message

Selvarathinam - 13 Jan 2006 17:01 GMT

Dear McGimpsey,

Sorry, If you use the ROUND(SUM(A2:A4),0), then the same syntax will be
effective.

Thanks,
Selvarathinam.

Reply to this Message

Peo Sjoblom - 13 Jan 2006 17:28 GMT

So how would that work if you had other decimal values as well, are you
implying that you would round all values to the nearest integer?
what if the values were in A2:A6 with 1 in A1

A2: =ROUND(A1/3,2)
A3: =ROUND(A1/3,2)
A4: =ROUND(A1/3,2)
A5: =ROUND(A1/4,2)
A6: =ROUND(A1/4,2)

using your formula

=ROUND(SUM(A2:A6),0)

it would return 1, that would make the error larger

Signature

Regards,

Peo Sjoblom

> Dear McGimpsey,
>
[quoted text clipped - 3 lines]
> Thanks,
> Selvarathinam.

Reply to this Message

JE McGimpsey - 13 Jan 2006 17:36 GMT

That's *only* effective if you're dealing in whole dollars. Consider:

A1: $1.03
A2: =ROUND(A1/3, 2) ==> $0.34
A3: =ROUND(A1/3, 2) ==> $0.34
A4: =ROUND(A1/3, 2) ==> $0.34
A5: =ROUND(SUM(A2:A4),0) ==> $1.00
A6: =SUM(A2:A4) ==> $1.02

Simply using ROUND() cannot guarantee that values will balance.

There are many techniques that can be used to "correct" models, but they
need to be evaluated for the particular approach that the model takes.

A better approach might be

A4: =A1-SUM(A2:A3) ==> $0.34

but determining which cell in A2:A4 should get the extra penny is not
always self-evident. And if there were 60 dividends instead of 3, the
remainders could give a very disproportionate answer. For instance:

A1: $1.03
A2: =ROUND(A1/70, 2) ==> $0.01
..
A70: =ROUND(A1/70, 2) ==> $0.01
A71: =A1-SUM(A2:A70) ==> $0.34

> Sorry, If you use the ROUND(SUM(A2:A4),0), then the same syntax will be
> effective.

Reply to this Message

Selvarathinam - 13 Jan 2006 18:16 GMT

Oops......

Sorry I got it wrong.

Anyway thanks for correcting me.

Regards,
Selvarathinam.

Reply to this Message

JE McGimpsey - 13 Jan 2006 19:56 GMT

Not a problem - keep posting. I, for one, generally learn better when
I'm wrong in public. Tends to stick a bit more...<g>

> Oops......
>
> Sorry I got it wrong.

Reply to this Message