Issue 112910

Summary: Data Sort using 3 criteria gives incorrect result.
Product: Calc Reporter: wheelz <peter.wheelz>
Component: formattingAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: elish, issues
Version: OOO320m12   
Target Milestone: ---   
Hardware: Mac   
OS: Mac OS X 10   
URL: http://www.tsw.net.nz/files/Wanganui Open 2010.ods
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---

Description wheelz 2010-07-04 01:52:34 UTC
In the linked Spreadsheet nav. to sheet "Master".
Select B5:I47
Data|Sort
Sort by Column I | descending
Then by Column H | descending
Then by Column G | descending

Result is generally ok except for H23 & H24. These are not sorted correctly.

This also occurred on Sheet "C Grade" with H6 & H7 being the affected data.
I6 & I7 are also out of sorts but I'm unsure how the previous sort criteria might affect this.

This sort criteria is used to ascertain score hierarchy on a "count back" system when there is a tie. 

This is my my first Bug Submission, so I hope I've done it correctly.

Regards,
Pete
Comment 1 Joe Smith 2010-07-04 16:04:08 UTC
Nothing official here, just my take...

I think it's a numerical problem, not a sorting problem: the values in I23 & I24
*look* the same, but they're not. As Calc compares them, I23 is a very tiny bit
less than I24, so it does not compare the values in column H.

I honestly can't tell you why they're different, or even how to see it. If you
try =I23=I24, Calc shows TRUE, and both =I23<I24 and =I23>I24 show FALSE.
=1-I23/296.16 gives zero, as does =1-SUM(F23:H23)/296.16.

However, if I change the formula in column I from
=IF(F5="";"";SUM(F5:H5))
to
=IF(F5="";"";ROUND(SUM(F5:H5);2))
--just rounding off the sum to the same number of places your data are already
limited to, so it should not change the answers, then I get the expected order
with your sort.

You can even see it if you copy cells G23:I24 and then paste-special with only
the values--no formulas--to a new sheet. If you sort those two rows, you'll see
the same sort order problem, but if you just save and reload that file, or edit
the values in the last column (just to re-enter them, not to change the value),
then the sort gives the expected order.

So it seems there's some invisible difference between the SUMs in cells I23 & I24.

Surely this is a nasty problem, even if it's not literally a bug; as far as I
know, it's just something that happens occasionally with any spreadsheet.

Changing to "precision as shown" is one way to protect against floating-point
gremlins, but it doesn't seem to help in this case.
Comment 2 Edwin Sharp 2013-07-31 08:35:33 UTC
Confirmed on Rev. 1507307 Win 7.
Column I sorts OK.
Column H,G do not sort OK.