I think Calc should show a hint that it uses the internally carried value for calculation. For example, on "mouse-over" should be displayed (e.g.): "internal calculation is done without rounding." Optimally, the indication should only appear if the result at hand differs from a result that would result if the displayed rounded values were used for calculation. And optimally, the link (see below) to the corresponding help function should then also be displayed. Background: I created an invoice/calc spreadsheet that calculated 2 different VAT rates for 2 amounts, and then added up those two results. The result was that the total was not the same as the total from the displayed values, but was 1 cent higher. And then I tried and puzzled **for about 1 hour.** https://help.libreoffice.org/7.1/en-US/text/scalc/guide/rounding_numbers.html To calculate with the rounded off numbers instead of the internal exact values Choose Tools - Options - LibreOffice Calc. Go to the Calculate page. Mark the Precision as shown field and exit the dialog with OK.
IMO WONTFIX. This is just a basics of spreadsheets.
(In reply to Mike Kaganski from comment #1) I'd agree that it is "just a basics of spreadsheets". But why not making life easier for people who haven't yet got the basics down?
(In reply to steffan.steffner from comment #2) > But why not making life easier for people who haven't yet got the basics > down? Because display is a limited resource (space), and hints tend to be intrusive, so we should not introduce such things that would only be useful to a small fraction of users only once, when they read this first time, and then only be a distraction for all.
(In reply to Mike Kaganski from comment #3) >would only be useful to a small fraction of users only once, when they read this >first time I believe that even experienced user might get caught by surprise, i.e. in case of a spreadsheet used for an invoice, which I think is a prominent example because in an invoice you certainly want to use the numbers **as displayed**. Because it depends on the actual numbers currently used whether there will be a different outcome on summing up numbers that "invisible to the user" carry more decimal places than can be seen on display. This is why I wrote in comment #1 "the indication should only appear if the result at hand differs from a result that would result if the displayed rounded values were used for calculation." I think this would be a useful hint for everybody, even if he/she in theory knows the pitfalls.
- imho - while calc doesn't achieve (decimal) correct results some warning / info / feedback to the user would be appropriate, more likely a dot / corner in affected cells than a mouseover, but my opinion will not help to convince @Mike Kaganski ... :-(
as an aside to this bug: when you have an hyperlink in your Writer document, you will get the notice "Strg-click to open hyperlink" when doing a mouse-over -- even though everybody who has little experience with 'Writer' for sure knows how to open hyperlinks. I find this notice helpful because -- if anything else -- it gives me the possibility to send my text containing hyperlinks to newbies who otherwise for sure wouldn't be able to open these. Is the mouse-over notice for hyperlinks intrusive? I don't think so. I think it makes live easier, especially for newbies.
(In reply to steffan.steffner from comment #6) This is completely unrelated. The tooltip that you refer to is not only "helpful for newbies": it also allows one to know where *hyperlinks* are, because otherwise you may simply not tell hyperlinks from other fields, or from other text (you may disable field shading, or customize links colors in Options|LO|Application Colors, or use similar colors for your text formatting, or just direct-format the hyperlinks). So this is comparing apples to oranges, and is unrelated.
(In reply to Mike Kaganski from comment #7) > (In reply to steffan.steffner from comment #6) > it also allows one to know where *hyperlinks* are, > because otherwise you may simply not tell hyperlinks from other fields, or > from other text If the reason for this was to just show the user that it is a hyperlink, then it would suffice to just show a hand symbol (as in Thunderbird), or show a hand symbol and the real URL (as for a PDF in Adobe Reader). It wouldn't be necessary to explicitly state "Strg-click to open" in LibreOffice Write, which makes the text of the mouse-over pop up significantly longer (e.g. Adobe Reader doesn't say "click to open link ...", but just shows you the hand symbol and the URL"). But I'm not looking out to annoy people here with endless reasoning. I'm just trying to say that IMHO this would be helpful and I don't think this would be intrusive to the user.
One last thought from my side: The tooltip could have a checkbox with a text saying "show again". So anybody who feels this tooltip is intrusive would only be intruded once.
what about a tooltip showing the user the 'internal value' normally hidden from his view and access, and maybe in green when it's a 'binary exact' value and in orange if it's a truncated or rounded 'endless fraction' which would inject inaccuracies into further calculations?
Please don't forget to CC @libreoffice-ux-advise when using the keyword needsUXEval.
Internally we calculate with 14 digits, see bug 96918, and display the result with or without rounding, recently challenged in bug 138920. So what you probably talk about is how a number is shown but I don't see the question here. Got an example?
Created attachment 171060 [details] calculated in the "normal" way
Created attachment 171061 [details] Calculated with "precison as shown"
Created attachment 171062 [details] Screenshot calculated in the "normal" way
Created attachment 171063 [details] Screenshot: Calculated with "precison as shown"
(In reply to Heiko Tietze from comment #12) > So what you probably talk about is how a number is shown but I don't see the > question here. Got an example? Yes, I will show here he example that made me search for 1 hour. It is about an incoice, and what is come down to is that I had 2 sums, one had to be added 16% tax (for 2020), and the other had to be added 19% tax (for 2021). And the displayed result of my invoice was that the numbers displayed in the invoice didn’t add up: The total sum was 1 Cent too high. So the calculations that were critical were: 469 x 0,16 = 75,0528 1.946,96 x 0,19 = 369,9224 Obviously when you add up those 2, you will get 444,9752 → which will be rounded up to 444,98 To clarify this: Every one of these 2 numbers will not be rounded, but the sum of these 2 will be rounded because 00,0052 will be rounded up to 00,01. And this was where the extra Cent came from. You know how accountants are: If the numbers they see on the paper don’t add up, they will be mad at you. And, as you know, the solution to this problem was, that I did as recommended in https://help.libreoffice.org/7.1/en-US/text/scalc/guide/rounding_numbers.html i.e. I let calc skip the internal decimal places that are not displayed as indicated in the above link >>“To calculate with the rounded off numbers instead of the internal exact >>values >> Choose Tools - Options - LibreOffice Calc. >> Go to the Calculate page. Mark the Precision as shown field and exit the >>dialog with OK.” See also my uploads “test - ‘normal.ods” and “test--'Precision as shown'.ods” , as well as screenshots of these two.
[Automated Action] NeedInfo-To-Unconfirmed
it's two different problems ... one is the logical problem with rounding in totaling calculations, e.g the conflict between selling two items for 1,02 bucks, each charged with 19 percent VAT resulting in - rounded down - two times 0,19 -> 0,38, vs. calculating the VAT for the full net amount of 2,04 bucks to - rounded up - 0,39 is a problem a spreadsheet can't solve, the user must be aware that there are pitfalls, check the correct way to calculate with his accountant, and take that into account ... (spreadsheets can be set up correctly for each schema, but not for both at the same time) another problem is with which values calc calculates, and how much of it is visible / accessible to the user, that would have been quite easy for this case, but is a real pain once you get behind 14 digits, i vote for 'enhance this',
(In reply to b. from comment #19) > it's two different problems ... > > one is the logical problem with rounding in totaling calculations, e.g the > conflict between selling two items for 1,02 bucks, each charged with 19 > percent VAT resulting in - rounded down - two times 0,19 -> 0,38, vs. > calculating the VAT for the full net amount of 2,04 bucks to - rounded up - > 0,39 is a problem a spreadsheet can't solve, the user must be aware that > there are pitfalls, check the correct way to calculate with his accountant, > and take that into account ... > > (spreadsheets can be set up correctly for each schema, but not for both at > the same time) Actually, in my case at hand, it would have been quite difficult to do the summing first, if there is 16% VAT on the first amount and 19% VAT on the second amount. Apart from that I completely agree that this is a well known problem, like i.e. here https://www.manager.io/guides/9499 But I think that most people would agree that in an invoice the numbers as displayed or "on paper" have to add up, even if this means that the result is mathematically less exact. The problem is that this case (see attachment "screenshot calculated in normal way" ) does not happen every day, so a person (like I) will be caught by surprise. Thus my request for a tooltip.
@steffan.steffner: 'But I think that most people would agree that in an invoice the numbers as displayed or "on paper" have to add up, even if this means that the result is mathematically less exact.' - yes, that's required for invoices, but no!, spreadsheets are not 'invoice machines', but 'universal tools', thus they need customizing for the special purpose one want's to use them. 'so a person (like I) will be caught by surprise. Thus my request for a tooltip.' - fully understand and support that, i see a 'gap in understanding' between developers well adapted and used to 'how that works' and it's difficulties and pitfalls, and users expecting things to 'work as it would make sense' (from their actual pov), imho implementing easier access to the things 'behind the scene' would help a lot.
"Precision as shown" is fooling you ;-). 1.01+1.01 is 2.02 but if you calculate without decimal places you get 2. This option is generic and applies to all value in the sheet. You expect highlighting of (calculated only?) cells with 1.0101 but not 1.0100 (given only two decimal points are shown). To not clutter the UI it needs to be an option, for example somehow combined with View > Value Highlighting perhaps with a brighter or darker color. Do you think average users not being aware of the danger that "Precision as shown" switch on this option and understand the outcome? Seems to me "Precision as shown" is a dangerous option and we need to hide it (what's the use case of it anyway) or at least put it into some expert section.
Created attachment 171117 [details] "Set precision as displayed" in Excel 2016 (In reply to Heiko Tietze from comment #22) > You expect highlighting of (calculated only?) cells with 1.0101 but not > 1.0100 (given only two decimal points are shown). To not clutter the UI it > needs to be an option, for example somehow combined with View > Value > Highlighting perhaps with a brighter or darker color. Note also that it would need to calculate everything twice, to know if a value actually shown would differ from value that would be shown in case of "precision as shown". It would be a considerable (x2!) performance penalty of an "innocent option". > Seems to me "Precision as shown" is a dangerous option and we need to hide > it (what's the use case of it anyway) or at least put it into some expert > section. At least Excel 2016 also buries it in some advanced section, and does not show it in its "Tell me ..." search results.
I’d like to get back to my example, as shown in the attachment (screenshot and .odt file)„test normal: the calculation at hand is an invoice, and all the figures show amounts in Euro (€): 469,08 + 1946,96 + 75,05 + 369,92 = 2861,02 If you take out your calculator, you will get the result 2861,01 I was frantically looking for more than 1 hour to find out which was the mistake I made in setting up the spreadsheet, and at the end I came to the conclusion that LibreOffice was a piece of crap and you couldn’t trust it with even the simplest calculations. I resorted to solve my problem by manually filling in the desired result 2861,01. Only to find out later I found out what it was all about. I can tell you that my thoughts were I will abandon LibreOffice because of this. And certainly, in response to Comment #22 and #23, what I am arguing for is not to move the function to some other place, I would just think it might be good to let the user know what happens behind the scenes **if the calculation at hand is affected**. And I would agree with comment #23 that in order to achieve this (=only show if it makes a difference) there is a performance penalty in that you have to do the calculation twice, one time with the normal precision (which is about 15 digits) and a second time with the decimal accuracy as shown. My best bet would be that this could take like 50 milliseconds on an average computer. And, sure enough, I myself don’t really need this tooltip, because I will know immediately. And to completely avoid this problem for my invoices, all I have to do is in my template for invoices to set the option “precision as shown”.
As a draft / proposal for this tooltip: - tooltip would be shown when doing a mouse-over on the cell of the spreadsheet where the result would be different - the text in the tooltip would be: “Note: Since Calc is internally calculating with more digits than you selected to display in your spreadsheet, the result at hand would here be different when using the numbers as displayed. The cells causing this are …….. See also here https://help.libreoffice.org/7.1/en-US/text/scalc/guide/rounding_numbers.html - Sure enough, the text would need to be truncated, and only when the user clicks on it he/she can see all of it - for not annoying the user, the tooltip would fade out after 2 seconds, if he/she has not clicked on the text; only if the cursor is moved away for a longer time, and then a mouse-over on the cell is done again, will the tooltip re-appear In case somebody doubts whether many more people except me have run into this problem, you can do a g**gle search for e.g. libreoffice calc summe falsch Genauigkeit wie angezeigt or libreoffice calc sum wrong precision as shown
Eike, what do you think? * Highlight/Mark calculated cells with imprecise results (why not show the correct result in this case?) * Remove/Hide/Warn for "Precision as shown" * Small feedback at the statusbar if this option is on * WF
(In reply to Heiko Tietze from comment #26) > Eike, what do you think? > > * Highlight/Mark calculated cells with imprecise results (why not show the > correct result in this case?) > * Remove/Hide/Warn for "Precision as shown" > * Small feedback at the statusbar if this option is on > * WF Sorry to be so blunt, you seem not to get it: - The option "Precision as shown" is **off** by default; - My problem was caused by "Precision as shown" being off, which is the default - there are no "incorrect results": there are results that are calculated with a bigger or smaller number of digits; - "precision as shown" thus causes a smaller number of digits to be used, since the usual internally used 15 (?) decimal places are deliberately cut off according to how many decimal places you choose to display in your respective cell of the spreadsheet; - my case (invoice) is a special case, which is nevertheless not a rare case, obviously; - in invoices, you want that the numbers "as displayed" or "on paper" add up, even if the result is mathematically less exact, and this is achieved by deliberately cutting off the decimal places that are usually internally used, but are not shown in the display according to the respective cell formatting you chose; - Removing the option "precision as shown" would cause that Calc is then unusable for invoices; If you read here: https://help.libreoffice.org/7.1/en-US/text/scalc/guide/rounding_numbers.html (at the bottom): To calculate with the rounded off numbers instead of the internal exact values Choose Tools - Options - LibreOffice Calc. Go to the Calculate page. Mark the Precision as shown field and exit the dialog with OK. “rounded off numbers” in the above paragraph means: Rounded off according to the formatting of the respective cell (which, obviously, in an invoice is usually 2 decimal places or https://help.libreoffice.org/7.1/de/text/scalc/guide/rounding_numbers.html Mit den angezeigten gerundeten Werten rechnen anstatt mit den internen genauen Werten Wählen Sie Extras - Optionen... - LibreOffice Calc. Wechseln Sie zum Register Berechnen. Markieren Sie Genauigkeit wie angezeigt und beenden Sie den Dialog mit OK. s.o. “angezeigte gerundete Werte” bedeutet ‘wie für die Formatierung der jeweiligen Zelle gewählt’, d.h. in einer Rechnung wird man die Anzeige üblicherweise für alle Zellen auf 2 Nachkommastellen formatieren oder runden (2 Stellen für Eurocent). Damit „Überraschungen“ vermieden werden, schreibt man jetzt Calc vor, dass auch für die interne Berechnung alle Stellen nach diesen 2 Nachkommastellen verworfen werden (=Genauigkeit wie angezeigt)
I should have given the exact details right from the beginning – so my fault: [calculation below: precision as shown is *off*] SUMME 1 (2020) 469,08 € +SUMME 2 (2021) 1.946,96 € +16% Mwst. (von Summe 1) 75,05 € +19% Mwst. (von Summe 2) 369,92 € = Gesamtbetrag 2.861,02 € This is the result when precision as shown is off (default). But the result is not wrong, it is **mathematically more exact**, because in reality the numbers are: in Euro: 469,08 + 1946,96 + 75,0528 + 369,9224 = 2861,0152 Thus, 2861,02 is only 0,0048 away from the exact result, whereas 2861,01 is 0,0052 away from the exact result. The problem thus comes down to: In an invoice, you don’t want the mathematically more exact result, you want results that add up with what is shown ‘on paper’. And if you take out your calculator, and you add up what is shown on paper, you get the result 2861,01. Hope that made it clear.
(In reply to steffan.steffner from comment #27 and comment #28) Your issue is clearly understood, don't worry. But note that using "precision as shown" is generally *not* a correct option. You assume that it would be what your accountants would expect, but this is not necessarily the case: the specific legislation may (and actually does) regulate what to do when imprecise numbers appear in calculations; some may ask usual rounding; some may require rounding to even; some may require truncation. It *actually* happens in practice; and misusing the dangerous and simplistic "precision as shown" as substitute for knowingly following rules that are set at your place at this time is a bad option. I would agree that hiding that option better, and not advertising it in help, or at least adding disclaimers, would be good. The proper way when filling "invoices" is not relying on implicit general-purpose software rounding rules, but to explicitly define proper rounding in proper places (=ROUND(A1/B2;2)), or use pre-made templates from your accountants, with such thigs prepared. And no, trying to implement what you suggest to make this "special case but not a rare case" is just misguided. (In reply to steffan.steffner from comment #24) > And I would agree with comment #23 that > in order to achieve this (=only show if it makes a difference) there is a > performance penalty in that you have to do the calculation twice, one time > with the normal precision (which is about 15 digits) and a second time with > the decimal accuracy as shown. My best bet would be that this could take > like 50 milliseconds on an average computer. I laughed when read that. You obviously do not realize what tasks spreadsheets are used for. It's common to have hundreds of thousands of formulas in a document. Some recalculations may take minutes. What you suggest is "dear heavy Calc users, please rest more, waiting while your spreadsheet is recalculating".
(In reply to Mike Kaganski from comment #29) > > The proper way when filling "invoices" is not relying on implicit > general-purpose software rounding rules, but to explicitly define proper > rounding in proper places (=ROUND(A1/B2;2)), or use pre-made templates from > your accountants, with such thigs prepared. And no, trying to implement what > you suggest to make this "special case but not a rare case" is just > misguided. I'd agree that probably the "correct" solution is to use the "ROUND" switch for every cell in the invoice. I can tell you that I use Calc for sending invoices to my customers, and if they check if everything is correct, they would probably just get out the calculator and type in what they see. Not that they would contact me because of 1 Eurocent difference, they might just think that my work is sloppy overall. So actually, for me to make spreadsheets for my invoices without the option "precision as shown" will make the task much more tedious, because instead of setting one option, I'll have to go into the details for every single cell. > > (In reply to steffan.steffner from comment #24) > > And I would agree with comment #23 that > > in order to achieve this (=only show if it makes a difference) there is a > > performance penalty in that you have to do the calculation twice, one time > > with the normal precision (which is about 15 digits) and a second time with > > the decimal accuracy as shown. My best bet would be that this could take > > like 50 milliseconds on an average computer. > > I laughed when read that. You obviously do not realize what tasks > spreadsheets are used for. It's common to have hundreds of thousands of > formulas in a document. Some recalculations may take minutes. What you > suggest is "dear heavy Calc users, please rest more, waiting while your > spreadsheet is recalculating". Before you start thinking to proceed by taking out altogether the option "precision as shown" from Calc, I would like to mention the following: I haven’t seen any post about people being in trouble because they activated “precision as shown”, and then forgot about. As I just tested, for every new document you create, the default setting for this option, which is “precision as shown” *off*, is set again. So, for being in trouble with this option, you would need to make a template, set this option, and then forget about it when you use this template. So, as you can see, I am really defensive now, and as I said, I don’t really need this tooltip anymore, since I am now an expert, at least for this very option, and I do agree that the “double calculation” penalty would matter for heavy users. So, I’d now make the call to make this bug a “WONTFIX” (before other bad things happen).
Suggest to put the option into a separate frame and to add an icon at the statusbar indicating the setting. Was thinking about to switch between on and off but this makes access to the potentially harmful option too easy. Steffan: I guess you disagree with the recommendation but otherwise it would be helpful to change the summary.
(In reply to Heiko Tietze from comment #31) > put the option into a separate frame Sorry, what is "frame" in this context? Do you have a mockup? > and to add an icon at the statusbar indicating the setting. +1
(In reply to Mike Kaganski from comment #32) > Sorry, what is "frame" in this context? Do you have a mockup? GtkFrame, just a separate section in the options dialog.
(In reply to Heiko Tietze from comment #31) > Suggest to put the option into a separate frame and to add an icon at the > statusbar indicating the setting. Was thinking about to switch between on > and off but this makes access to the potentially harmful option too easy. > > Steffan: I guess you disagree with the recommendation but otherwise it would > be helpful to change the summary. Well, as I said, reporting a bug on this issue was never to serve me per se - because once you know about Calc using more digits than shown, and that this can make a difference for your result, you don't really need any hint on this. So my idea was, I have been frantically searching for on hour, I want to spare this to other users of Calc, who might want to quit LibreOffice because they might think it is not a reliable product. I have to admit, I am one of those users who do not have an extended knowledge of spreadsheets, but just use it randomly for minor purposes, which is my case writing invoices to customers. And my best bet would be, that there are a lot of Non-Expert-Users who might need some info if they come across such a case. I would also agree that not every invoice will have to be handled in the way I describe it, but to me it seems to be the most plausible way for everyday invoices, because I would probably check if the numbers as shown on paper add up, if I received one that I had to pay. And, I certainly think that the hint from Mike to instead use the ROUND function is very useful,like in my case e.g ROUND(B19*0,16;2)on only 2 places in my invoice where I calculate the respective VAT rates, which are the only 2 places that could cause the effect I described. So, actually I don't have an opinion on this any more. Even if you decided to scrap the "precision as shown" function, it wouldn't make me quit using LibreOffice, since there is the ROUND function alternatives that I know now thanks to the hint from Mike.
I’d have one more proposal: In case a cell in formatted as currency (e.g. in Euro or Dollar), and the operation of the cell is multiplication or division, a hint would be displayed as follows: You might want to use the ROUND function to round to 2 decimal places, especially in case of an invoice, since Calc is internally calculating with a higher number of digits, which sometimes gives you unwanted results. Everything else could be handled as described in Comment #25 (fade out, text truncated, etc.)
In addition to fade out, text truncated, you could have a checkbox "don't show again"