Loading
Report thread as spam

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: =A10.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

This question was started by Mondou 3 years ago

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: =A10.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!)

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

Your sample... A1: 570.45 B1: =A15% (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(A15%,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 at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc

This response was posted by GS 3 years ago

> > >

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: =A10.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!)

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

Your sample...   A1: 570.45   B1: =A15%  (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(A15%,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 !

This response was posted by Mondou 3 years ago

in equations.

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

often returns a rounded value (CELL A1):  Enter: 570.45, returns; $ 570.45 (CELL B1):  Enter: =A10.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(A15%,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.

This response was posted by joeu2004 3 years ago

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.

-- Garry

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

This response was posted by GS 3 years ago

It happens that joeu2004 formulated :

in equations.

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

often returns a rounded value (CELL A1):  Enter: 570.45, returns; $ 570.45 (CELL B1):  Enter: =A10.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(A15%,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().

-- Garry

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

This response was posted by GS 3 years ago

Mondou explained on 2/25/2011 :

> > >

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: =A10.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!)

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

Your sample...   A1: 570.45   B1: =A15%  (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(A15%,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 !

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!)

-- Garry

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

This response was posted by GS 3 years ago

using the ROUND() function, it's not necessary to use the function with SUM().

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.

This response was posted by joeu2004 3 years ago

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(,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(A15%,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.

-- Garry

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

This response was posted by GS 3 years ago

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.

This response was posted by joeu2004 3 years ago

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/microsoft.public.excel.misc/browse_frm/thread/7261f3d71bbc3cb9/b1bd8e9798c529ca?q=floating+point+sum+group:microsoft.public.excel.*+author:joeu2004%40hotmail.com#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.

This response was posted by joeu2004 3 years ago

joeu2004 formulated the question :

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/microsoft.public.excel.misc/browse_frm/thread/7261f3d71bbc3cb9/b1bd8e9798c529ca?q=floating+point+sum+group:microsoft.public.excel.*+author:joeu2004%40hotmail.com#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!

-- Garry

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

This response was posted by GS 3 years ago

ROTFL!

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.

This response was posted by joeu2004 3 years ago

joeu2004 brought next idea :

>

ROTFL!

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.

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.

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?

-- Garry

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

This response was posted by GS 3 years ago

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.

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

functions need to be applied?

Yes, that is quite apparent.

This response was posted by joeu2004 3 years ago

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.

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

This response was posted by joeu2004 3 years ago

joeu2004 presented the following explanation :

>

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.

> >

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.

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!

functions need to be applied?

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.

regards,

-- Garry

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

This response was posted by GS 3 years ago

joeu2004 brought next idea :

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.

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

-- Garry

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

This response was posted by GS 3 years ago

joeu2004 laid this down on his screen :

B1: =ROUND(A15%,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.

-- Garry

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

This response was posted by GS 3 years ago

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

Yes, that is quite apparent.

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.

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!

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.

This response was posted by joeu2004 3 years ago

joeu2004 was thinking very hard :

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

Yes, that is quite apparent.

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.

I gathered that! I just saw no point in creating a confrontation...

> >

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!

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

But I believe that is clearly not true. You're wrong there!

>

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.

They were made in context to the OP's scenario ONLY. You constued them to be general statements all along. I was hoping you picked that up by my demonstrating with your example scenarios...

>

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.

There was no falacy in my statement because it was not a 'general statement'. Also, I demonstrated my point by doing the same thing with your substitute scenario. My approach worked perfectly there as it did with the OP's numbers. Read it again to confirm...

It was YOU who determined I was wrong based your (erroneous) presumption I was making a 'general statement'.

>

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

Agreed...

>

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.

You already stated this wasn't necessary in Mondou's scenario. -Leads me to think you're more confused about how numbers work than you believe I am. Make up your mind! You haven't revealed any I didn't already know, whether you choose to believe that or not. Taking my statement out of context doesn't make it wrong as stated.

I guess we'll just continue to violently agree...

-- Garry

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

This response was posted by GS 3 years ago

Other excel threads