Sorting on formula results behaving oddly for me

[MarcC]

I’m trying to sort some calculated results, and the sort is behaving oddly to say the least. Following is a cut-down, representative example that illustrates what I’m experiencing. (I’m only allowed one screenshot in this post, so please bear with me.)

Plain numbers are in A2:A7.

Each cell in column C calculates its value from column A. In this example, each cell of D shows the–simplified–formula that’s in column C. I.e. C2 contains the formula =A2.)

In column B, cells B11:B16 are just copies of what’s in column C, though the top and bottom halves are swapped. E.g. cell B11 contains =C5. That’s what “From C5” is intended to indicate.

So, A2 contains the number 12, C2 contains =A2 (so also 12), though it’s B14 that contains =C2 (so 12).

I want to sort B11:B16, so I highlight it, then select Data/Sort Descending (with a request to extend the selection, which I accept).

I expect to get 75, 40, 38, 19, 12, 2.

Instead, this is the result in B11:16 (sorry, no screenshot):

#REF!, 19, 40, 0, 2, 75

I figure that I’m misunderstanding something about data representation, so would welcome some clarification, and perhaps a recommendation on how to accomplish what I’m trying to do here.

[mikekaganski]

The real problem here is the not realizing that a reference like =C5 is a relative reference. It is shown like this only for user’s convenience (so that user sees the C5 and can see where the formula points to); but user needs to understand, that =C5 means “the value of a cell 6 rows above, one column right” - because that’s what "C5 relative to B11" means to the program.

Now consider what happens when you sort the range (without the “Update references when sorting range of cells” workaround), and the formula “the value of a cell 6 rows above, one column right” moves to the cell B14. It still means that relative address to the program, but for B14, the cell 6 rows above, one column right is C8, which is empty - and gives 0.

The same logic holds for the formula that was originally in cell B15. It meant “12 rows above, one column right”, but when moved to B11, the reference should point to non-existent C-1, hence #REF!.

Not saying that you should not use “Update references when sorting range of cells”, but understanding the above is crucial for working with spreadsheets.

[MarcC]

Thanks! Helps knowing why it acts this way.

[erAck]

For these specific cases of seeing and understanding groups of relative references it may help to temporarily switch to the Excel R1C1 address notation (under Tools → Options → Calc → Formula, Formula Options Formula syntax) that displays C5 relative to B11 as R[-6]C[1] and C6 relative to B12 is displayed the same. An absolute reference to $C$5 is R5C3.

[mariosv]

I think the option to ‘Update references when sorting range of cells’

should do it.
But if you do other sorting operations in the spreadsheet, they w’ll be affected.

[MarcC]

Thank you, this seems to have taken care of the problem, with no ill-effects elsewhere!