Stop Rounding Currency

[Mondou]

Does anybody know how to stop Excel from rounding numbers in
equations.

For example; When I need to add 2 taxes to an amount, it often returns
a rounded value

(CELL A1): Enter: 570.45, returns; $ 570.45
(CELL B1): Enter: =A1*0.05 , returns; $ 28.52 ... (Tax1 = 5%)
(CELL C1): Enter: =(A1+B1)*0.085 , returns; $ 50.91 ... (Tax2 = 8.5%)
(CELL D1): Enter: =SUM(A1:C1) , returns; $ 649.89 ... when it should
obviously be $ 649.88

I know a penny isn't much but when you have a bunch of figures on a
sheet they do add up.

Is their a way around this ... cells are formatted as currency with 2
decimals?

Thanks in advance

 

[GS]

Mondou has brought this to us :

Does anybody know how to stop Excel from rounding numbers in
equations.

For example; When I need to add 2 taxes to an amount, it often returns
a rounded value

(CELL A1): Enter: 570.45, returns; $ 570.45
(CELL B1): Enter: =A1*0.05 , returns; $ 28.52 ... (Tax1 = 5%)
(CELL C1): Enter: =(A1+B1)*0.085 , returns; $ 50.91 ... (Tax2 = 8.5%)
(CELL D1): Enter: =SUM(A1:C1) , returns; $ 649.89 ... when it should
obviously be $ 649.88

I know a penny isn't much but when you have a bunch of figures on a
sheet they do add up.

Is their a way around this ... cells are formatted as currency with 2
decimals?

Thanks in advance

I do something similar and don't have this issue. Change the format to
'Number' set to 2 decimals and using thousands separator. When I
calculate each tax I get the correct total. Maybe the difference is
that your Tax2 is calculating the amount+Tax1. (IOW, you're taxing a
tax - which is a criminal practice we don't do here!<g>)

So...
A1: 570.45 (constant value - ergo no return)
B1: =A1*5% (returns 28.52)
C1: =A1*8.5% (returns 48.49)
D1: =SUM(A1:C1) (returns 647.46)

Your sample...
A1: 570.45
B1: =A1*5% (returns: 28.5225; displays: 28.52)
C1: =(A1+B1)*8.5% (returns: 50.91266; displays: 50.91)
D1: =SUM(A1:C1) (returns: 649.88516; displays: 649.89)

modified to...
A1: 570.45
B1: =ROUND(A1*5%,2) (returns 28.52)
C1: =ROUND((A1+B1)*8.5%,2) (returns 50.91)
D1: =SUM(A1:C1) (returns 649.88)

 

[Mondou]

Mondou has brought this to us :









I do something similar and don't have this issue. Change the format to
'Number' set to 2 decimals and using thousands separator. When I
calculate each tax I get the correct total. Maybe the difference is
that your Tax2 is calculating the amount+Tax1. (IOW, you're taxing a
tax - which is a criminal practice we don't do here!<g>)

So...
  A1: 570.45 (constant value - ergo no return)
  B1: =A1*5% (returns 28.52)
  C1: =A1*8.5% (returns 48.49)
  D1: =SUM(A1:C1) (returns 647.46)

Your sample...
  A1: 570.45
  B1: =A1*5%  (returns: 28.5225; displays: 28.52)
  C1: =(A1+B1)*8.5%  (returns: 50.91266; displays: 50.91)
  D1: =SUM(A1:C1)  (returns: 649.88516; displays: 649.89)

modified to...
  A1: 570.45
  B1: =ROUND(A1*5%,2)  (returns 28.52)
  C1: =ROUND((A1+B1)*8.5%,2)  (returns 50.91)
  D1: =SUM(A1:C1)  (returns 649.88)

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Works Great ! ... Thanks Garry.

You'd think think that EXCEL would incorporate this round function in
the Currency Format.

As for the Double Taxing, I agree with you, it's a criminal
offense ... but the Canadian government are experts on taxing !

 

[joeu2004]

Does anybody know how to stop Excel from rounding numbers
in equations.

Ironically, apparently you want just the opposite: you want to ensure
that Excel does round results.

For example; When I need to add 2 taxes to an amount, it
often returns a rounded value
(CELL A1):  Enter: 570.45, returns; $ 570.45
(CELL B1):  Enter: =A1*0.05 , returns; $ 28.52 ... (Tax1 = 5%)
(CELL C1):  Enter: =(A1+B1)*0.085 , returns; $ 50.91 ...
(Tax2 = 8.5%)
(CELL D1):  Enter: =SUM(A1:C1) , returns; $ 649.89 ...
when it should obviously be $ 649.88

When you display to 2 decimal places, Excel rounds the displayed
value.

For example, 570.45*5% is 28.5226, which you would like rounded to
28.52, the displayed number.

Likewise, 570.55*5% is 28.5275, which I presume you would like rounded
to 28.53 -- again, the displayed number.

There are two ways to make that happen:

* Explicitly round expressions that you intend to be accurate to the
displayed precision.

* Set the "Precision as displayed" (PAD) calculation option (Tools >
Options > Calculation in XL2003).

I do not recommend PAD for many reasons. But if you choose to
experiment with it, be sure to make a copy of your Excel file first.
PAD can change constants irreversibly -- one of the reasons not use
PAD.

I think it would be better to explicitly round expressions. Even
though that can seem tedious, it gives you the flexibility of not
rounding when you do not want to.

So your formulas should be as follows:

B1: =ROUND(A1*5%,2)
C1: =ROUND((A1+B1)*8.5%,2)
D1: =ROUND(SUM(A1:C1),2)

Rounding D1 is done for a slightly different reason: reliability.
Because of the way that Excel stores numbers and performs arithmetic,
even simple addition and subtraction is not always accurate to 2
decimal places, even if the operands are.

For example, 10.1-10-0.1 is not exactly zero. But
ROUND(10.1-10-0.1,2) is.

 

[GS]

After serious thinking Mondou wrote :

Works Great ! ... Thanks Garry.

You'd think think that EXCEL would incorporate this round function in
the Currency Format.

As for the Double Taxing, I agree with you, it's a criminal
offense ... but the Canadian government are experts on taxing !

Well, I happen to live in Canada and nowhere is the PST charged on GST.
Where are you located that this is happening? We basically conform to
tax harmonization whether your province/territory charges the 2 taxes
separately or not.

Sounds to me like you misunderstand how to apply these taxes. That
said, your calculations should follow my first example.

 

[GS]

It happens that joeu2004 formulated :

Ironically, apparently you want just the opposite: you want to ensure
that Excel does round results.


When you display to 2 decimal places, Excel rounds the displayed
value.

For example, 570.45*5% is 28.5226, which you would like rounded to
28.52, the displayed number.

Likewise, 570.55*5% is 28.5275, which I presume you would like rounded
to 28.53 -- again, the displayed number.

There are two ways to make that happen:

* Explicitly round expressions that you intend to be accurate to the
displayed precision.

* Set the "Precision as displayed" (PAD) calculation option (Tools >
Options > Calculation in XL2003).

I do not recommend PAD for many reasons. But if you choose to
experiment with it, be sure to make a copy of your Excel file first.
PAD can change constants irreversibly -- one of the reasons not use
PAD.

I think it would be better to explicitly round expressions. Even
though that can seem tedious, it gives you the flexibility of not
rounding when you do not want to.

So your formulas should be as follows:

B1: =ROUND(A1*5%,2)
C1: =ROUND((A1+B1)*8.5%,2)
D1: =ROUND(SUM(A1:C1),2)

Rounding D1 is done for a slightly different reason: reliability.
Because of the way that Excel stores numbers and performs arithmetic,
even simple addition and subtraction is not always accurate to 2
decimal places, even if the operands are.

For example, 10.1-10-0.1 is not exactly zero. But
ROUND(10.1-10-0.1,2) is.

Since the 2 cells containing the taxes have been processed using the
ROUND() function, it's not necessary to use the function with SUM().<g>

 

[GS]

Mondou explained on 2/25/2011 :

Works Great ! ... Thanks Garry.

You'd think think that EXCEL would incorporate this round function in
the Currency Format.

As for the Double Taxing, I agree with you, it's a criminal
offense ... but the Canadian government are experts on taxing !

I figured it out! You're in Quebec. No surprise there, -go figure! This
has nothing to do with Canada Revenue Agency since Quebec has been
given rights to manage their own revenues, AND make up their own rules
about it. (Ugh!)

 

[joeu2004]

Since the 2 cells containing the taxes have been processed
using the ROUND() function, it's not necessary to use the
function with SUM().<g>

Although you are correct for this example, you are incorrect in
general.

For example, if A1 is 570.55, SUM(A1:C1) seems to be 650.00. But
IF(SUM(A1:C1)-650=0,TRUE) returns FALSE(!) [1].

And I had already provided another example.... If A1 is 10.10, A2 is
-10, and A3 is =SUM(A1:A2), =IF(A3=0.1,TRUE) is FALSE(!).

The problem is that most non-integer numbers (and most integers
greater than 2^53) cannot be represented exactly in the binary form
(called binary floating-point) that Excel uses for storing numbers and
for arithmetic [2].

For many real-world examples from users whom I have assisted over the
past 6 year, search Google Groups for posting from me with the keyword
floating-point.

-----
Endnotes

[1] Re: IF(SUM(A1:C1)-650=0,TRUE) returns FALSE. But
=IF(SUM(A1:C1)=650,TRUE) does return TRUE. The difference is due to
the dubious heuristic poorly described under the misleading title
"Example When a Value Reaches Zero" at http://support.microsoft.com/kb/78113.

[2] 10.1 is represented internally as exactly
10.0999999999999,996447286321199499070644378662109375.

So 10.1-10 is exactly
0.0999999999999996,447286321199499070644378662109375

But the constant 0.1 is represented as exactly
0.100000000000000,0055511151231257827021181583404541015625

Likewise, returning to Mondou's example, when A1 is 570.55, SUM(A1:C1)
is exactly 649.999999999999,8863131622783839702606201171875.

Obviously that is not exactly 650. But ROUND(SUM(A1:C1),2) is.

Note: Usually, you will be unable to see these infinitesimal
"errors" (anomalies) because Excel displays only up to 15 significant
digits. I convert the entire binary representation. You can
certainly see the difference in 10.1-10.

 

[GS]

All that was very interesting, and the effort you went to is
appreciated. I do, however, still stand that if all the values in the
SUM() have been calculated with the ROUND() function then using ROUND()
in SUM() is pointless. IOW, if you used ROUND(<value>,2) with all of
your examples then there would be some ambiguity as to the results.

[1] OP's context
A1: 570.55
B1: =ROUND(A1*5%,2) (returns 28.53)
C1: =ROUND((A1+B1)*8.5%,2) (returns 50.92)
D1: =SUM(A1:C1) (returns 650.00)
E1: =SUM(A1:C1)=650 (returns TRUE)

[2]A
A1: =ROUND(10.1,2) (returns 10.10)
B1: =ROUND(10,2) (returns 10.00)
C1: =A1-B1 (returns 0.10)
D1: =(A1-B1)=C1 (returns TRUE)
E1: =(A1-B1)=0.10 (returns FALSE)
F1: =ROUND(A1-B1,2)=0.10 (returns TRUE)
G1: =SUM(A1:B1) (returns 20.10)
H1: =(SUM(A1:B1))=20.10 (returns TRUE)

[2]B
A1: 10.10
B1: 10.00
C1: =ROUND(A1-B1,2) (returns 0.10)
D1: =(A1-B1)=C1 (returns FALSE)
E1: =(A1-B1)=0.10 (returns FALSE)
F1: =ROUND(A1-B1,2)=0.10 (returns TRUE)
G1: =SUM(A1:B1) (returns 20.10)
H1: =(SUM(A1:B1))=20.10 (returns TRUE)

Summary:
Hoping not to offend, working with your individual examples doesn't
return consistent, reliable results. Results are dependant on where the
ROUND() function is used. It doesn't matter how Excel
evaluates/represents 0.1. What matters is how we need to work with the
value. That precludes, then, that context of use dictates how ROUND()
should be applied.

Excel evaluates =1*10% as 0.1000000000000000000000000000000000000000...
Excel evaluates =1/10 as 0.1000000000000000000000000000000000000000...
For all intents both are 0.1 in every human application. Why Excel
would represent it as something else is not just ludicrous, -it's
insane!

In the case of the OP's scenario, the context of use of ROUND() for tax
calculations returns consistent, reliable results. As you can see, the
SUM() function behaves consistently in all the above.

 

[joeu2004]

I do, however, still stand that if all the values in the
SUM() have been calculated with the ROUND() function then
using ROUND() in SUM() is pointless.

You are wrong; I demonstrated it in my previous examples. There
really is nothing more that I can say. Some people just have to learn
the hard way.

 

[joeu2004]

You are wrong; I demonstrated it in my previous examples.
There really is nothing more that I can say.  Some people
just have to learn the hard way.

Here is one final example.

As I said before, simply do a search of Google Groups for one my many
responses on the subject. It was not difficult to find this example:
http://groups.google.com/group/micr...+author:[email protected]#b1bd8e9798c529ca.

In a nutshell, C1:C7 contain the following constants:

C1: 6,055,337.25
C2: -611,570.68
C3: 0.00
C4: -5,259,682.31
C5: 1,119.12
C6: 146,826.12
C7: 39.68

Since those are constants, they are already rounded to 2 decimal
places.

Suppose C8 contains =SUM(C1:C7). That displays 332,069.18.

But IF(C8=332069.18,TRUE) returns FALSE(!).

If C8 contains =ROUND(SUM(C1:C7),2), the IF expression returns TRUE as
expected.

If you format C8 to 9 decimal places, you will see the problem with
the unrounded SUM formula. The result is actually 332,069.180000001.

 

[GS]

joeu2004 formulated the question :

Here is one final example.

As I said before, simply do a search of Google Groups for one my many
responses on the subject. It was not difficult to find this example:
http://groups.google.com/group/micr...+author:[email protected]#b1bd8e9798c529ca.

In a nutshell, C1:C7 contain the following constants:

C1: 6,055,337.25
C2: -611,570.68
C3: 0.00
C4: -5,259,682.31
C5: 1,119.12
C6: 146,826.12
C7: 39.68

Since those are constants, they are already rounded to 2 decimal
places.

Suppose C8 contains =SUM(C1:C7). That displays 332,069.18.

But IF(C8=332069.18,TRUE) returns FALSE(!).

If C8 contains =ROUND(SUM(C1:C7),2), the IF expression returns TRUE as
expected.

If you format C8 to 9 decimal places, you will see the problem with
the unrounded SUM formula. The result is actually 332,069.180000001.

I guess you still don't understand my point as stated, BUT you seem to
continue to support it with your examples. I don't dispute your claims
regarding individual values. I merely point out the ambiguities in the
context of their use as you example, and the use of the SUM() function
within that context.

Because the values ARE constants rather than calcs, my point is to use
ROUND() with SUM(). IF the values were calculations (as with the OP's
example) then using ROUND() with SUM() isn't necessary.

MY POINT IS THAT ROUND() GOES WITH CALCS! You continue to prove this
with your examples while you tell me I'm wrong. Sorry, you just don't
get it!

 

[joeu2004]

Sorry, you just don't get it!

ROTFL!

Because the values ARE constants rather than calcs, my
point is to use ROUND() with SUM(). IF the values were
calculations (as with the OP's example) then using
ROUND() with SUM() isn't necessary.

That's absurd! But if it helps you see your fallacy, try this:

C1: =ROUND(6055337.25+0.004,2)
C2: =ROUND(-611570.68+0.004,2)
C3: =ROUND(0.00+0.004,2)
C4: =ROUND(-5259682.31+0.004,2)
C5: =ROUND(1119.12+0.004,2)
C6: =ROUND(146826.12+0.004,2)
C7: =ROUND(39.68+0.004,2)

C8: =SUM(C1:C7)
C9: =ROUND(332069.18+0.004,2)

Again, C8 will display 332,069.18 when formatted to 2 decimal places.
But =IF(C8=C9,TRUE) returns FALSE(!).

However, if C8 is =ROUND(SUM(C1:C7),2), then =IF(C8=C9,TRUE) returns.

As I explained before, the reason for the difference can be seen by
formatting the unrounded formula in C8 to 9 decimal places.

(But as I showed in previous examples, the difference cannot always be
seen, due to Excel's display limit of 15 significant digits.)

If makes no difference whether C1:C7 are constants, or they are
expressions that equal those constants when explicitly rounded to 2
decimal places.

Rounding to 2 decimal places returns the same binary representation as
the constants.

 

[GS]

joeu2004 brought next idea :

ROTFL!


That's absurd! But if it helps you see your fallacy, try this:

C1: =ROUND(6055337.25+0.004,2)
C2: =ROUND(-611570.68+0.004,2)
C3: =ROUND(0.00+0.004,2)
C4: =ROUND(-5259682.31+0.004,2)
C5: =ROUND(1119.12+0.004,2)
C6: =ROUND(146826.12+0.004,2)
C7: =ROUND(39.68+0.004,2)

C8: =SUM(C1:C7)
C9: =ROUND(332069.18+0.004,2)

Again, C8 will display 332,069.18 when formatted to 2 decimal places.
But =IF(C8=C9,TRUE) returns FALSE(!).

However, if C8 is =ROUND(SUM(C1:C7),2), then =IF(C8=C9,TRUE) returns.

As I explained before, the reason for the difference can be seen by
formatting the unrounded formula in C8 to 9 decimal places.

(But as I showed in previous examples, the difference cannot always be
seen, due to Excel's display limit of 15 significant digits.)

If makes no difference whether C1:C7 are constants, or they are
expressions that equal those constants when explicitly rounded to 2
decimal places.

Rounding to 2 decimal places returns the same binary representation as
the constants.

I'm not disputing any of this! You seem to have invented the notion
(with yourself) that I advocate NEVER using ROUND() with SUM(), and
this is absolutely NOT TRUE. My initial comment was "since ROUND() was
used to calculate the taxes in the OP's example that using ROUND() with
SUM() wasn't necessary". You agreed! Now here you cite a DIFFERENT
SCENARIO to which I have no problem with using ROUND() with SUM()
because THE SCENARIO WARRANTS IT. The OP's scenario does not warrant
it. -End of claim-

At no time anywhere or ever have I implied that (generally speaking)
Round() doesn't need to be used with SUM(). You seem to think
otherwise.

What I have advocated is the under certain conditions it is not
'necessary' to use ROUND() with SUM(), and have chosen some of your
examples to demonstrate where those conditions apply IMO. Nowhere did I
claim YOU were wrong.

<FWIW>
I migrated to Excel v4.x from Lotus123. Since then I have become well
versed (and well studied) with the ROUND functions and their use. I
have been using them in world-wide cost accounting projects for over 40
years. Do you not think I know how numbers work OR when these functions
need to be applied?<g>

 

[joeu2004]

The OP's scenario does not warrant it [rounding SUM]

Mondou's __example__ does not warrant it.

But I demonstrated that his __scenario__ does warrant it with a
different example, e.g. when A1 is 570.55.

What I have advocated is the under certain conditions
it is not 'necessary' to use ROUND() with SUM()

Yes, as long as all values in the sum are __integers__ no greater than
2^53.

But with __non-integers__, it is nearly impossible to predict when
arithmetic operations will result in the same binary representation as
the constant equivalent to the displayed value.

Ergo, with __non-integers__, it is prudent to explicitly round the
result of any expression that is intended to have a specific precision
(e.g. dollars-and-cents).

Do you not think I know how numbers work OR when these
functions need to be applied?

Yes, that is quite apparent.

 

[joeu2004]

Yes, as long as all values in the sum are __integers__
no greater than 2^53.

..... As long as all values __and__ their intermediate sums and their
final sum are integers no greater than 2^53.

 

[GS]

joeu2004 presented the following explanation :

The OP's scenario does not warrant it [rounding SUM]

Mondou's __example__ does not warrant it.

But I demonstrated that his __scenario__ does warrant it with a
different example, e.g. when A1 is 570.55.

This example did not use ROUND() to calc the taxes, and so I agree that
ROUND() should be used to calc SUM().

I also demonstrated this example using ROUND() to calc the taxes and
not using ROUND() to calc SUM(). This shows that both approaches return
the same results.

Yes, as long as all values in the sum are __integers__ no greater than
2^53.

No arg here!

But with __non-integers__, it is nearly impossible to predict when
arithmetic operations will result in the same binary representation as
the constant equivalent to the displayed value.

Ergo, with __non-integers__, it is prudent to explicitly round the
result of any expression that is intended to have a specific precision
(e.g. dollars-and-cents).

No arg here, either!

Yes, that is quite apparent.

You also seem to be quite versed in this subject! Our back&forth has
been a good exercise! Thanks for that...

I very well remember doing this with punch cards back when everything
was binary, computers were the size of a room (or 2), AND spreadsheets
were numerous sheets of a columnar pad taped together side-by-side.
Much of the grunt work had to be done manually and so one gets familiar
with the nuances of working with numbers very quickly.<g>

regards,

 

[GS]

joeu2004 brought next idea :

.... As long as all values __and__ their intermediate sums and their
final sum are integers no greater than 2^53.

I know what you meant...

 

[GS]

joeu2004 laid this down on his screen :

B1: =ROUND(A1*5%,2)
C1: =ROUND((A1+B1)*8.5%,2)
D1: =ROUND(SUM(A1:C1),2)

Rounding D1 is done for a slightly different reason: reliability.
Because of the way that Excel stores numbers and performs arithmetic,
even simple addition and subtraction is not always accurate to 2
decimal places, even if the operands are.

I also see your point for using ROUND() with SUM() for reliability
reasons, regardless of how the values were calc'd/stored. I would
default to using this approach with user solutions where I can't
control inputs.

 

[joeu2004]

Not surprisingly, you seem to have misunderstood my meaning.

I meant: yes, I do not think you know how numbers work [and] when
these functions need to be applied.

No arg here, either!

So it would seem that we have been in violent agreement.

But I believe that is clearly not true.

Previously, you wrote....

"Since the 2 cells containing the taxes have been processed using the
ROUND() function, it's not necessary to use the function with
SUM()." [Fri, 25 Feb 2011 19:23:43 -0500]

And....

"I do, however, still stand that if all the values in the
SUM() have been calculated with the ROUND() function then using
ROUND() in SUM() is pointless." [26 Feb 2011 02:04:30 -0500]

And....

"IF the values were calculations (as with the OP's example) then using
ROUND() with SUM() isn't necessary".

Those are all very general statements about calculations.

A "calculation" is an "expression", and Mondou's example involved non-
integers, and Mondou intended the results to have a specific
precision.


You wrote on 26 Feb 2011 11:57:11 -0500:

My initial comment was "since ROUND() was used to
calculate the taxes in the OP's example that using
ROUND() with SUM() wasn't necessary". You agreed!

I agreed that you were correct __by_coincidence__ because of the
specific numbers used in Mondou's example.

But I quickly stated that I did not agree with you __in_general__, and
I demonstrated your fallacy using Mondou's scenario with different
numbers.

(After that, I moved on to other scenarios because I felt they
demonstrated the same __concept__ more readily.)

If you have the experience that you claim, you know that we do not
write solutions for specific numbers. Instead, algorithms should be
correct for the full range of expected numbers.

ROUND(SUM(A1:C1),2) is needed in Mondou's scenario because with other
numbers, the result of SUM(A1:C1) alone will not exactly match the
displayed value (showing only 2 decimal places), and that might trip
up Mondou later on.

I really cannot say that any more clearly.