Sort Excel by derived column then by other column

Question

Excel for Mac v15.37 on OSX 10.11.6.

Here is a minimally reproducible example:

  |   A   |   B    |   C    |
|=|=======|========|========|
|1| First | Second |  F-S   |
|2|  3.4  |  4.0   | =A2-B2 |
|3|  3.6  |  4.2   | =A3-B3 |
|4|  3.5  |  4.1   | =A4-B4 |

Sort by C Then by A.

The output is:

  |   A   |   B    |   C    |
|=|=======|========|========|
|1| First | Second |  F-S   |
|2|  3.4  |  4.0   | -0.6   |
|3|  3.6  |  4.2   | -0.6   |
|4|  3.5  |  4.1   | -0.6   |

The expected output is:

  |   A   |   B    |   C    |
|=|=======|========|========|
|1| First | Second |  F-S   |
|2|  3.4  |  4.0   | -0.6   |
|4|  3.5  |  4.1   | -0.6   |
|3|  3.6  |  4.2   | -0.6   |

If I copy column C then paste it back in as "values", so the cell contains the literal -0.6 rather than the formula, then it sorts just fine. But I don't want to have to do that.

How can I get this to sort correctly?

Answer 1

It's a floating point precision issue.

3.4 - 4.0 is actually something like -0.6000000000001

and

3.5 - 4.1 is actually something like -0.5999999999999

Basic stuff. http://floating-point-gui.de/

The solution was to round the output column.

C3 = ROUND(A3 - B3, 2)