Incorrect SUM value unless "Precision as shown" is checked

[PlanetEarth]

Based upon my recommendation, my client replaced a corrupt MS Office installation with OpenOffice. Everything was (relatively) fine until he submitted a four-page spreadsheet for a $1.3 million bid on a project--only to be told his calculations were incorrect compared to what Excel showed when the bid was submitted.
I no longer have the exact numbers, but Calc's calculations were lower than what Excel showed, unless I put a check in the "Precision as shown" box and increased the decimal places to "4". Only with those options changed did Calc's calculation match Excel's. I understand Calc is not supposed to calculate using rounded numbers, but it seemed to be doing so anyway, and this spreadsheet involved hundreds of calculations for items that, in some cases, cost fractions of a penny. Multiply that by thousands, and those rounding errors add up. These rounding errors almost cost my client the project (since it appears he doesn't know how to add), and resulted in him buying MS Office 2007.
At any rate, it seems "Precision as shown" should be ENABLED by default, for all users during the installation. Increasing the number of decimal places might help some people, too. My main goal is to make sure no one else has to explain why their calculations using this otherwise fine software don't match what Excel's show.
I don't know if anyone else has had this problem when dealing with thousands of "fraction-of-a-penny" calculations multiplied thousands of times and added up to a sum of more than $1.3 million, but if you don't think you've had this problem, you might want to open the same spreadsheet in Excel and see if the numbers match.
Other than this, I'm a supporter of OpenOffice, and as a computer consultant, I'm switching as many of my customers to it as I can. I just don't want to lose any more to MS Office if I can help it!

Steve

[kingfisher]

If you can produce an example, that would be a worthy bug report.

I've just done a simple test with .1251 in one cell multiplied in another by 4. The correct result (.5004) was displayed when I increased the number of decimal places in the second cell to 4. I do NOT have "precision as shown" selected and it was not necessary to display the 4 decimal places in the first cell.

It is possible to increase the default number of decimal places using the same dialogue, i.e. Tools >Options >OO Calc> Calculate.

[PlanetEarth]

Thanks, Kingfisher.
I think you'd need a few dozen more calculations to see the errors start to creep in, but I'll try to get a copy of the file. I saved it after I made the changes to Calc, and while that shouldn't have changed the formulas, I don't know if he's done anything else to the file in the past two weeks (especially after he went back to Excel).
For what it's worth, I did try the file with just increasing the number of decimal places, and while that got it closer to the Excel "standard", it still wasn't exactly the same. Only enabling "Precision as shown" did the trick.

Steve

[kingfisher]

Is it possible that Calc was correct and there was error in Excel?

[confuseling]

Wouldn't be the first time. Windows 3.1's calculator, when asked what 2.01 - 2 was, would get the answer wrong.

[Dave]

Other than this, I'm a supporter of OpenOffice

You'll forgive me, but I've grown tired of that mantra.

Question: Are the figures those seen, or those in storage? You say you have to "increase... the decimal places to "4"." Does that alter the actual calculation, or the amount on which the calculation is based? Some actual figures showing the discrepancy [a spreadsheet can be loaded up here with fictitious amounts] would give leverage to the argument one way or the other. I'd suggest one with Calc and the same with Excel showing the difference, if any. Any finite example will do, showing values and possibly formulas. Otherwise, it's a guessing game.

Also, as often pointed out, people here are users, not developers, so such observation without data is not really useful.

David.

[PlanetEarth]

I have the file, and tested it again myself, comparing Cal 2.4 against Excel 2003. Unfortunately, I was able to confirm that unless "Precision as shown" is ENABLED, Calc appears to shave a few pennies off the calculations here and there, until the entire bid is off by almost $32000 at the end!
I have submitted the file for your review. Column D shows the 5% increase added to the cost of each item (the descriptions of which I've omitted). Column H shows the cost of each item before the 5% increase, and if you'll do some manual calculations, you'll see that even at this point, some of the individual calculations in column D are incorrect. Added together, the overall total is way off.
To see for yourself, modify the "UNIT PRICE/Markup" field's multiplier with "Precision as shown" enabled and disabled, and you'll see what I mean. For example, in the file I've submitted, the markup is "1.05", or 5%. Change that to "1.06" in cell D3 and drag that formula down to D211.
Since I don't know if you've got "Precision as shown" enabled or not, I've removed the grand total formulas. If I hadn't, you would have ended up with different totals than what I'm describing.
Please let me know what's going on with this, if you can. I've determined that increasing the number of decimal places doesn't seem to help, so please disregard my previous statement on that.
Also, even though I've set these posts to "Notify me when a reply is posted", I don't seem to get notified, so I'll check back as often as I can.
Thanks for your help on this!

Steve

[kingfisher]

To save people checking every item, could you indicate which ones are in error? Those I've checked so far are correct, including a rounding up of one item.

[Dave]

I see manually entered amounts rather than calculated amounts at the bottom, where there is supposed to be a difference. I see formulas using SUM() where that is not necessary. I see no reason for the complaint. Can you point out cells where there has been a calculation that is not what it should be from the figures shown?

David.

[PlanetEarth]

On a few of the prices that are below $1, there is obvious rounding occurring. I know that could also be "fixed" by showing more decimal places, but this really isn't about individual cells. The grand total is off, and I don't know where or why, but all I know is that by enabling "Precision as shown", the problem is resolved.
I've already stated why I put the totals there myself, and I didn't create the formulas, my client did. He created them because he saw there was a problem and was trying to find out what was happening. I'm merely passing on what he did in the hopes that someone here can benefit from it.
My point is that the same file calculates correctly in Excel, and regardless of how poorly it was designed, I'm trying to find out why so I can keep this from happening again if it's a flaw in the program. I'm not a Calc or Excel expert; I'm just the messenger.
Thanks.

Steve

[kingfisher]

I can confirm that the total alters if "Precision as shown" is enabled. It changes from $13,965,063.00 to $13,966,945.25, a difference of $1,882.25. The Excel total is given as $13,996,945.25. Is that a typo? If not, there is an additional $30,000.00 of error.

[PlanetEarth]

Yes, Kingfisher, that's a typo, and I apologize for that. Excel 2003 shows the total as $13,966,945.25.
Thanks for catching it.
Any idea what the culprit may be?

Steve

[kingfisher]

Without a painstaking analysis, one can't say where the fault is. An educated guess is that the lower total may be the sum of the true values and the greater total may be the sum of the rounded values. This is something I would ask about on the Calc mailing list. A developer might be able to point you in the right direction.

Edit: I've done a calculation. Column I contains a running total. Column J contains the results without precision as shown. Column K contains the results with precision as shown. Column L contains the differences.

[Villeroy]

So the rounding error increases row by row. I never used "Precision as displayed" nor did I ever understand why it is in Excel and Calc. If you want rounding, then use rounding functions at the adequate step of your calculation.
btw: The sheet has some obsolete SUM() functions where a single number is summed up, e.g. SUM(G3*1.05) and SUM(result1-result2). Does not hurt, but obfuscates a little bit.

[PlanetEarth]

Thanks, Kingfisher and Villeroy.

I was sort of hoping the developers monitored this forum, but I guess I can post the problem in the mailing list, too, when I get time. I appreciate you looking into where the whole thing starts to get screwy (if I may use a technical term).

Villeroy, I know the sheet has some obsolete SUM functions, but thanks for letting me know. My client is an "old school Excel user", and he put them in as a way to find out what was going on with the formulas and to try to find out where the problem was. He gave up and switched to Microsoft Office 2007, when he became too frustrated, unfortunately.

All I was really hoping to do with this post was to get "Precision as shown" ENABLED by default in any new release of Calc (if this really is a problem, and it appears to be)...unless there's a specific reason why the user wouldn't need it enabled. If it really taxes the processor on large spreadsheets, I understand leaving it off, but perhaps the user could be alerted about this issue?

As a consultant, I know quite a bit about how the average user would set up the "average" Excel spreadsheet (which would be very similar to this), and it seems to me, this is how the average user would create it. If Calc is miscalculating the totals by default, then I would rather err on the side of slower performance than wrong calculations. People may not like the software if it seems slow, but they'll hate it if it gives them incorrect answers--especially when they're basing multi-million dollar business decisions upon those answers.

I'll just keep enabling it on my clients' installations as I move them from MS Office to OpenOffice.

Thanks for the help, everyone.

Steve

[huw]

"Wrong" in this case means "does not match Excel's result".

"Precision as shown" means calculate with the displayed (rounded) figures, not to full accuracy (exact).

Together these two facts imply Excel is also set similarly, either just in this case, or by default (which I doubt). This, at least, needs to be confirmed or ruled out before a bug in Excel or Writer Calc can be looked for.

Edit: oops, wrong app.

[Villeroy]

Fortunately, "Precision as shown" is saved individually with the document. It is not a global setting. If you really want it for all new documents (File>New>Spreadsheet, Ctrl+N), set up your individual default template which includes this setting.
Shift+F11 ... save template name
File>Templates>Organize..., pick your template, [commands...] "Set as default template"

btw: What tells you that Calc gives a wrong result? It does exactly what you told it to do. It screwed up your numbers. The effect of cumulated rounding errors with "Precision as shown" is the same as in Excel, the more precise result is the same in Calc, Excel and Gnumeric (G. does not know this offending option).

[PlanetEarth]

What tells you that Calc gives a wrong result? It does exactly what you told it to do.

Yes, "wrong" in this case means Calc did not give my client the same result as the spreadsheet software that's (unfortunately) installed on more than 90% of computers in this country. It did not give him the same result as his clients were getting, nor did it give him the same results as those who oversee his work were getting. It almost cost him a $13.8 million project, and it embarrassed him.

Excel 2003 has "Precision as displayed", which (according to Microsoft): "Permanently changes stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed." This option is not enabled by default.
Calc's "Precision as shown" is also not enabled by default. In theory--and to the average person--their calculations should match. They don't. And, since Excel is the de facto standard in this country (and most others), it is used at the baseline against which other programs are measured, right or wrong.

I'm not going to argue whether Excel is better than Calc or whether your spreadsheet can kick my spreadsheet's butt. All I'm saying is that if people are hesitant to switch to open source software, they have to be shown it's as good--or better than--what they're using without forcing them to change too much. Asking my clients to blindly accept Calc's default calculations as "more accurate" or "better" than Excel's default calculations isn't going to help much if my clients then have to ask the people who are viewing their work to do the same. All my client knows is that Calc didn't give him what every one else said he should have. "Precision as shown" would have given him what Excel had already calculated, without "Precision as displayed" enabled.

Thanks for the tip about setting the default for "Precision as shown" (and other Calc options), Villeroy. I can use that.

Steve

[PlanetEarth]

By the way, again, I'm a big fan of this software, and I'm trying to switch my clients from Microsoft Office to OpenOffice. I'm not trying to start a fight between MS Office and OpenOffice (...or Windows vs. Mac, Blu-ray vs. HD-DVD or "The Partridge Family" vs. "The Brady Bunch").

I like Calc, and will continue to use it and help the entire project as I can. There are many things OO does better; there are some areas in which it could probably use some work. Of course, the same can be said for MS Office. As long as no one takes offense when an OpenOffice app is inevitably compared to its Microsoft counterpart, I think we'll be fine.

Thanks.

Steve

[maurizio]

Here is another example of incorrect sum which is solved using "Precision as shown". I spent a lot of time trying to find a fix for this. I was expecting to find this option on cell format dialog.

Maurizio

[huw]

The effect of "Precision as shown" setting

How is it incorrect? Using the "precision as shown" setting is telling Calc to round at every step. In any multi-step calculation that will usually give a different result to the conventional approach of rounding just once, at the end.

[keme]

How is it incorrect? Using the "precision as shown" setting is telling Calc to round at every step. In any multi-step calculation that will usually give a different result to the conventional approach of rounding just once, at the end.

Well, neither approach is entirely unquestionable.
To the accountant there is no subdivision of the penny, so any single transaction involving money should be rounded to two decimals at most. Using more decimals is incorrect because the subdivision doesn't exist in currency.

Budgeting and cost calculations, on the other hand, needs to reflect total cost across a range (project limits, budget period, etc.). If there's an intermediate calculation on detail level, and no transaction occurs at that detail level, rounding in that intermediate stage is incorrect.

IMHO, using the "precision as shown" for rounding is bad practice in most cases. Rather, use the spreadsheet functions. Then the choice of whether to round or not is more of a conscious choice. Furthermore, debugging is far more efficient that way, because errors are more easily spotted.

[huw]

Thank you keme. I knew accountants sometimes do round, and sometimes don't, I just didn't know the circumstances. That's a good explanation of the issues behind this thread.

[DavB]

Trying to set up a spreadsheet to track my golf scores. No decimal points or leading/following zeros. Pretty simple.
I set up with column for total strokes hole and putts, then at the end a =sum() function listing all the cells to total.
The totals in both are incorrect.

This is for nine holes:

8 3 8 3 8 3 3 2 7 2 5 2 7 3 3 2 8 50 18

Odd columns (8) are strokes, even columns (3) are putts. Totals should be 57 and 20. I have "Precision as shown" checked.

I like shaving the strokes, but when my biggest competitor is myself it really does no good.

And help will be greatly appreciated.

[FJCC]

It is hard to say what is wrong without the actual spreadsheet. My guess is that some of you cells are formatted as text. Select the menu item View -> Value Highlighting. Numbers will be blue and formula results will be green. Black designates text. If there is any, change the cell format to a number then reenter the data.

[RusselB]

DaveB: Entering the data you supplied, except for the totals, then using the sum function for the totals, I get totals of 57 & 20, respectively

[acknak]

Yes, "wrong" in this case means Calc did not give my client the same result as [Excel]. ... It almost cost him a $13.8 million project, and it embarrassed him. ...

And this is precisely why you'll find lots of recommendations from us here to AVOID using OO for critical tasks in a mixed (OO/MS) software environment.

OO tries to give equivalent results as MS Office, and especially Excel, with the same inputs, but obviously there can be no guarantee. At best you have to take time to compare OO's results to MS Office; you might as well just use MS Office to start with. Although, doing a comparison and tracking down any discrepancies can uncover errors, so it's not always a waste of time.

If you can put together a sheet where Excel and Calc give different answers, then it could be submitted as a bug and I expect the OO developers would look into it and bring it in line with Excel--if it makes sense to. But it has to demonstrate a clear difference, and it would be even better if it was clear what the source of the difference is.

BTW, I haven't read the whole thread, so maybe you've checked, but the biggest source of discrepancies is Calc's different treatment of "numeric text". Calc will happily ignore such values where Excel will convert them to numbers and include them in the calculations.

[jrkrideau]

Thanks, Kingfisher and Villeroy.

All I was really hoping to do with this post was to get "Precision as shown" ENABLED by default in any new release of Calc [/q]
Why? This may work in some cases particularly when dealing with monetary values but it is totally unreasonable when dealing with many other types of data. Certainly it is not advisable for any engineering or scientific calculations.

If Calc is miscalculating the totals by default, then I would rather err on the side of slower performance than wrong calculations.

Calc is not miscalculating anything that we have seen so far. As Keme points out it is a conscious choice to use one of the other. Which is used is dependent on the intended use use of the results.

I'll just keep enabling it on my clients' installations as I move them from MS Office to OpenOffice.

Well if your clients are doing any civil engineering calculations that sounds like a good way to kill somebody.

[Daiwe01]

Excerpt from http://office.microsoft.com/en-us/excel ... 18870.aspx

Set rounding precision
Applies to: Excel 2007

NOTE: Using the Precision as displayed option can have cumulative calculation effects that can make your data increasingly inaccurate over time. Use this option only if you are certain that the displayed precision will maintain the accuracy of your data.

[coewar]

Wow... look at this .. I can repeat it over and over and on different computers.. with ver 3.4.1 (using this old version because of other bugs I've had with updates)

In a new workbook, somewhere enter the number: 2000
Then in another cell next to it (under or to the right): -1995.08
And then in the 3rd cell, click on the Summation button. or just enter the formula to do the =SUM(xxx;yyy) for those 2 columns.

The result it shows you in the formula cell: 4.92
But if you COPY and PASTE VALUE into another cell, you get: 4.92000000000007


Other values cause same thing.. you can adjust up or down by cents and it will output some different inaccurate calculation.

AMAZING!!!
FIX IT.