Forum Discussion
Excel bug Data sorting largest to smallest
I have some problems by sorting data in excel.
First sorting by column M Largest to Smallest, than by column L also Largest to Smallest.
In one case sorting is not successfully (Marked font color RED).
Can someone explain this to me?
https://drive.google.com/file/d/1n_IWsw9YH6FDxXnFm_wWwJEVQIN9AxRu/view?usp=sharing
5 Replies
Try this formula:
=SORT(IF(B5:M62 = "", "", B5:M62), {12,11}, {-1,-1})Hope this helps.
It gives exactly the same (i.e. wrong) result
plus doesn't sort in place.
I don't know why you get those results. With my formula, I get these results like in the image.
I have apply my formula in cell O5.
Odd indeed, but it's due to some minute rounding difference. The first 593.1 is considered greater than the second one. Normally you would see that in the 15th decimal but in your file that's not the case either. Both numbers are equal to the eye, though internally they are not. Google for "floating-point arithmetic" to read more about it.
When you notice something like that, wrap your formulas in column M in the ROUND function and the problem will go away. In M5 enter:
=ROUND(SUM(G5:L5),1)and copy down. Re-apply the custom sort.
Without sample file or at least screenshot it's hard to say what's wrong.
