Sort function Seems to Have a Glitch

[jasman922]

I have an odd occurrence using my sort function. Attached is a hard-numbered version of the table I am sorting. In it, I have sorted by columns (no header) in the following order:

AC [A to Z]
AM [A to Z]
AL [A to Z]
AA [Largest to Smallest]
AJ [Largest to Smallest]

All seems correct, but if you look at rows 5 & 6 (with "Central Florida" and Syracuse" in column C), the sort should have been reversed. The first four criteria are identical, so the fifth should drive the sort, and, with Syracuse (Row 6) having the larger value, should be higher in the sorted table. I can't see why this happens (and frankly, am surprised I noticed it, but it is there).

Note that if you swap the Order in the fifth criteria from [Largest to Smallest] to [Smallest to Largest], other rows will swap, but these two rows will not.

If anyone has an idea of why this happens and how to fix it, I would be appreciative of the help.

Jasman922

[MrShorty]

Another case of floating point error (https://www.excelforum.com/groups/ma...nd-errors.html ). You don't say where the values in column AA come from or how they are calculated, but it appears that Central Florida's 0.6 in AA is slightly larger than Syracuse's 0.6 in AA. So, the sort algorithm correctly puts Central Florida above Syracuse. To see this, put something like =(AA6-AA5) (the parentheses are important) into a convenient cell and format as scientific. I see a small, non-zero number (-1.11E-16).

Since I don't know what column AA represents or how it is calculated, I cannot make a specific recommendation. The usual first strategy would be to nest the current calculation inside of a ROUND() function that will round the final result to whatever level of precision is appropriate for the calculation.

[queuesef]

It appears to be correct. Put your data into a database, perhaps access. Move the 5 Columns you listed to the first through fifth positions in your order on a database query. Then Sort as per your listings.
I believe that the 'Sorting' goes in priority of your 1st Column, then 2nd etc.

[jasman922]

Thanks for taking the time to look at this.

FYI, the formulae used to generate the hard-numbered cells in the spreadsheet (assuming we are looking at row 5) are:

AC is =IF(Z5<0,"A",IF(Z5=0,IF(OR(X5>0,Y5>0),"B",IF(OR(X5<0,Y5<0),"C","F")),"D"))
AM is =IF(AK5<>AB5,"conflict","")
AL is =IF(AI5<0,"A",IF(AI5=0,IF(OR(AG5>0,AH5>0),"B",IF(OR(AG5<0,AH5<0),"C","F")),"D"))
AA is =ABS(X5-Y5)
AJ is =ABS(AG5-AH5)

It could be a floating point error, but it seems that the differential is so large that this wouldn't be the issue. Also, since I hard numbered the values in the posted spreadsheet, I think that would be obviated, don't you?

Jasman922

[jasman922]

Queuesef:

Thanks for the reply. How does it seem to be correct to you, since the first four are equal, but the fifth entry is in the wrong order. Secondly, why do the other entries change if you flip the order of sorting but not those two?

Jasman922

[jasman922]

MrShorty:

Before you ask:

X is =H5/(H5+I5)-0.5
Y is =J5/(J5+K5)-0.5
AK is =IF(AG5>AH5,"Fav","Dog")
AB is =IF(X5>Y5,"Fav","Dog")
AI is =AG5*AH5
AG is =L5/(L5+M5)-0.5
AH is =N5/(N5+O5)-0.5

I realized those were imbedded in the other formulae.

Thnaks again for even looking at this diversion.

Jasman922

[MrShorty]

Also, since I hard numbered the values in the posted spreadsheet, I think that would be obviated, don't you?

You and I would probably like to think that hard numbering the values into the spreadsheet would obviate the problem. Whatever you thought you did to avoid the problem, I can still enter =(AA6-AA5), and the result is a non-zero value -1E-16, so those two values are not exactly equal. When Excel gets to that sort level, it is sorting those two values from large to small. Without knowing exactly how you put the sample sheet together, I cannot say much more than that. If you want the sort engine to treat AA5 and AA6 as exactly equal, you must figure out how to get the exact same value (approximation) into both cells. In your sample sheet, if I hand enter 0.6 into both cells, then they are seen as exactly equal, and the sort algorithm will go on to the next level. Since I don't know how you hard numbered the values into the spreadsheet, I don't know why Excel sees them as two different values.

As for the formulas, I see a lot of room for floating point errors (there are a lot of ratios/fractions and a lot of adding/subtracting of decimal fractions). I don't think it should come as any surprise that there are floating point errors in those calculations.

[jasman922]

MrShorty:

You have been too kind with your time! I accept that the error is a floating point one. It was the quirkiness of the non-apparent inequality (as Excel calculated them, anyway) that drove me to post the question.

I hard numbered the posted spreadsheet using the PasteValues function, so I guess it was a bit like the fox guarding the hen house, trusting the hard numbers to be as they looked. Odd that the cells show 0.6, but the functionality changes when you did that manually. I guess 0.6 ain't what it used to be!

Thanks again.

Jasman922