Bug 148070

Summary: Spurios penny in a sum() total of cells declared as £currency
Product: LibreOffice Reporter: Neil Nation <nnation>
Component: CalcAssignee: Not Assigned <libreoffice-bugs>
Status: RESOLVED NOTABUG    
Severity: normal CC: nnation
Priority: medium    
Version: 7.2.5.2 release   
Hardware: IA64 (Itanium)   
OS: Windows (All)   
Whiteboard:
Crash report or crash signature: Regression By:
Attachments: Screen shot of spreasheet

Description Neil Nation 2022-03-18 13:24:57 UTC 
Description:
Row of £currency values, each without pence ( .oo )
The total given by sum() shows .01  in the total

Actual Results:
Calculate a pecentage addition to a currenct value
add a variable currency value to the above to give a zero pence/cents row total declared as currency
Sum the round pounds ( zero pence) row totals
The grand total has .01 in the resulting figure !!

Expected Results:
The Sum Total of zero pence currency values should have zero pence


Reproducible: Always


User Profile Reset: No



Additional Info:
[Information automatically included from LibreOffice]
Locale: en-GB
Module: SpreadsheetDocument
[Information guessed from browser]
OS: Windows (All)
OS is 64bit: no
Comment 1 Neil Nation 2022-03-18 13:29:01 UTC 
Created attachment 178954 [details]
Screen shot of spreasheet
Comment 2 Mike Kaganski 2022-03-18 13:56:54 UTC 
(In reply to Neil Nation from comment #1)
> Screen shot of spreasheet

I tried to increase the precision of N2:N15 on your screenshot, and for some reason, I couldn't. ;)
Comment 3 Neil Nation 2022-03-18 14:30:19 UTC 
(In reply to Mike Kaganski from comment #2)
> (In reply to Neil Nation from comment #1)
> > Screen shot of spreasheet
> 
> I tried to increase the precision of N2:N15 on your screenshot, and for some
> reason, I couldn't. ;)

I increased precision of the row currency values to 3, with this result - Neil

£2,351.998
£3,444.000
£2,100.002
£4,080.000
£2,172.004
£5,039.998
£1,788.004
£936.002
£1,692.000
£5,004.004
£0.000
£0.000
----------------
£28,608.012
Comment 4 Mike Kaganski 2022-03-18 14:47:30 UTC 
(In reply to Neil Nation from comment #3)

So you see that the calculations are correct, and the displaying two digits fooled you into believing that your data is "round", which it is not. The non-displaying remainder accumulated to give you what you see.

1. Always attach a sample spreadsheet to bugs, not some screenshots, which are absolutely useless for others to reproduce and diagnose the problem.
2. When working with floating-point numbers, be prepared to inaccuracies [1] (but this is not your case here).
3. Know that limiting displayed precision does not change your data, so when you only show two decimals, the rest of the data still plays role. There is a special configuration to change this: Options/Calc/Calculate/General Calculations: [ ] Precision as shown; but I advise you to *not* use that, since it may result in unexpected inaccuracies elsewhere, and you better use ROUND explicitly where you know that your data needs to be rounded to some specific precision.

Resolving NOTABUG.

[1] https://wiki.documentfoundation.org/Faq/Calc/Accuracy
Comment 5 Neil Nation 2022-03-18 14:59:30 UTC 
Mike

Thank you for your quick response and explanation

This is the first time I've noticed this condition in many many years of using Libre Office and it's previous incarnations. Perhaps it was all of the individual elements having dot zero zero in their results that made the total with dot zero one stand out.

Best Regards and have a good week-end, wherever you are located.

Neil