How do I stop a calculation rounding up

[Lynneth]

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

 

[JE McGimpsey]

See

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

 

[Selvarathinam]

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.

 

[JE McGimpsey]

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

 

[Selvarathinam]

Dear McGimpsey,

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

Thanks,
Selvarathinam.

 

[Peo Sjoblom]

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

 

[JE McGimpsey]

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

 

[Selvarathinam]

Oops......

Sorry I got it wrong.

Anyway thanks for correcting me.

Regards,
Selvarathinam.

 

[JE McGimpsey]

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