How do I stop a calculation rounding up

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

See

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

In article <Lynneth.21kmby_1137151800.4059@excelforum-nospam.com>,
Lynneth <Lynneth.21kmby_1137151800.4059@excelforum-nospam.com> wrote:

> 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

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


Lynneth wrote:

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

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



In article <1137157854.619044.206460@g47g2000cwa.googlegroups.com>,
"Selvarathinam" <s.selvarathinam@gmail.com> wrote:

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

Dear McGimpsey,

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

Thanks,
Selvarathinam.

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


--

Regards,

Peo Sjoblom


"Selvarathinam" <s.selvarathinam@gmail.com> wrote in message
news:1137171686.306540.103880@g47g2000cwa.googlegroups.com...
>
> Dear McGimpsey,
>
> Sorry, If you use the ROUND(SUM(A2:A4),0), then the same syntax will be
> effective.
>
> Thanks,
> Selvarathinam.
>

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


In article <1137171686.306540.103880@g47g2000cwa.googlegroups.com>,
"Selvarathinam" <s.selvarathinam@gmail.com> wrote:

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

Oops......

Sorry I got it wrong.

Anyway thanks for correcting me.

Regards,
Selvarathinam.