Apache OpenOffice (AOO) Bugzilla – Issue 17053
New rounding/display for billing/financial calculation
Last modified: 2013-02-07 22:34:45 UTC
As concluded in Issue 16637 (http://www.openoffice.org/issues/show_bug.cgi?id=16637): *** Robert Black Eagle wrote: Currency calculations get complicated, as was pointed out, so I would think there could be two versions of a SUM function -- one that would round only the display of the unrounded total). In any case, it needs to come out right and, as he points out, it does not. *** Rainer (Bielefeld) wrote: You see: the solution for 1 special case is absolutely bad for another case, as also Roberts examples showed. On the other hand, we can think about some enhancements like - show, that cell contains rounded value - calculate only with visible decimals *** Thanks for the conclusion and the info about the different rounding standards. My expectation was something like "round up every digit if it's >=5 and that up the number of displayed digits". Perhaps the approach with two versions of the sum function is suitable. But since it has been realized now that there should be done something, I'll leave the how-to in your capable hands. But, although this is a feature issue, let me quote once more what describes my feeling of the situation : "In any case, it needs to come out right and [...] it does not." (At the moment...) And if Excel makes the same, in my opinion, mistake - well one more point for OOo. Jan
Hi All, What's about using Tools - Options - Spreadsheet - Calculate - Precision as shown and Decimal places ? This should fix the problems described. Nevertheless, I reassign this to Bettina for evaluating the Feature request. Frank
Robert, in issue 16637 you mention Texas taxes. Do you have any reference for that? I found some descriptions that are somewhat ambiguous ("go out to the third decimal place", which may or may not mean rounding), but the tables at http://www.window.state.tx.us/taxinfo/taxforms/01-forms.html, which seem to be official, use normal rounding.
I should have been more specific. Texas law allows local sales taxes to round in that fashion. The example I gave comes from the local taxes in Fort Worth. I have seen other situations where that kind of "rounding" was found. It makes it hard to program taxes and the funny rounding is only effective up to $1.00 in purchases. Thus we have a table of taxes up to $0.99 and then can use normal rounding after that. When writing a program for taxes for a spreadsheet, I have simply used a VLOOKUP table for taxes under $1.00 and a formula after that. For the situations mentioned, I have used multi-column solutions, with the actual rate in one column and the rounded rate in a "hidden" column, then summed the hidden column. It works, but I can see a use for a "Sum rounded values" as distinct from "Sum then round." However, I have always just done it in a series of columns, some of which are hidden for ease or printing reports. I understand the problem, but am so used to Visicalc and StarCalc's methods (same in Excel) that I've always written my own programming to handle it. It would have been very helpful to have a function that did a RSUM (Round then sum the rounded values) as opposed to a rounded sum method. I think this is something that we need some user input on. There are lots of situations where two different summing methods would be helpful, but there are also lots of questions. For example, do you round to displayed values or round to a different value then sum?
I've given some thought on this and recall my solutions in the past with Excel. My normal treatment was to write a program to do the rounding the way I wanted and simply work around it. I would suggest something like: SUMDI (Sum of displayed digits only -- result automatically rounded by individual items being rounded before summing) SUM (Does the same thing as current sum) SUMSP (Sum with rounding to a specified number of decimal places independent both of internal representation and displayed values). SUMRND (Sum rounded after adding all values so that following usage of the sum would not carry extraneous decimals). As far as I can recall, this should solve all the problems I have encountered by enabling a user to avoid writing his/her own program. As far as I can tell, none of these is a difficult programming issue.
I completely forgot one rounding technique used by engineers to minimize the "e" problem ("error range" -- maximum expected error). I have used it and am embarrassed that I left it out. SUMSCI (or SUMENG) rounds up or down to the nearest even number when the next significant digit is 5, so that 6.95 becomes 7.0 and 6.85 becomes 6.8. The sum of a list of these functions minimizes errors more than any other rounding technique.
OpenOffice.org Issue Tracker - Feedback Request. The Issue you raised has the status 'New' pending further action, but has not been updated within the last 4 years. Please consider re-testing with one of the latest versions of OOo, as the problem(s) may have already been addressed. Either use the recent stable version: http://download.openoffice.org/index.html or consider trying the new OOo 3 BETA (still in testing): http://download.openoffice.org/3.0beta/ Please report back the outcome so this Issue may be Closed or Progressed as necessary - otherwise it may be Resolved as Invalid in the future. You may also wish to search for (and note) any duplicates of this Issue that may have advanced further by checking the Issue Tracker: http://www.openoffice.org/issues/query.cgi Many thanks, Andrew Cleaning-up and Closing old Issues as part of: ~ The Grand Bug Squash, pre v3 ~ http://marketing.openoffice.org/3.0/announcementbeta.html
To grep the issues easier via "requirements" I put the issues currently lying on my owner to the owner "requirements".