Sorting in numbers

You are not doing anything wrong, this is a result of how Numbers sorts. When you sort, cell references get readjusted so they continue to refer to the cells they referred to before the sort. For example, if a formula in Row 4 references cell B3 and, when you sort the table, B3 moves to B10, the reference becomes B10. In your case, this makes your running total all screwed up because it is jumping all over the place.

You will need references that don't move. INDIRECT, ADDRESS and OFFSET are the usual functions for that.

J Edmondson wrote:
I keep a running tally of my checking account. Column A is date of transaction. Column B is description. Column C is amt of transaction and column D is a running balance. Column D is a formula that takes the previous cell above and adds or subtracts to Column C to keep the balance. When I add transactions sometimes out of date order, I go to re-sort A by date. Numbers messes up the formula. It sorts the formula weirdly and knocks the totals out of whack. I am sorting on selected cells. Is there something special I should be doing?

Hi J,

Welcome to Apple Discussions and the Numbers '09 forum.

Numbers keeps track of cells referenced in formulas, and maintains the reference to the same cell at its new address. What you need to do special is write the formulas so that they are independent of that full address cell reference.

If all cells referenced are in the same row as the cell containing the formula, that's a simple matter of omitting the row part of the address. For references to cells in a different row, it requires some form of indirect addressing.

Here's an example. It requires at least one header row to provide a fixed location for the base cell ($A$1) from which an offset is specified. I would suggest using two header rows to allow the starting balance to be placed in a non-sorting (header) row.

I'm assuming that the amounts in column C will be negative for withdrawals and positive for deposits.

Enter the formula below in D3, and fill down to the last row of the table.


=IF(ISBLANK(C),"",OFFSET($A$1,ROW()-2,COLUMN()-1,1,1)+OFFSET($A$1,ROW()-1,COLUMN ()-2,1,1))




Regards,
Barry

PS:
OFFSET is discussed (with examples) on p. 219 of the iWork Formulas and Functions User Guide. This guide, and the Numbers '09 User Guide may be downloaded via the help menu in Numbers. Both are recommended.
B

J,

The explanations that badunit and Barry have given you on how references are adjusted as a result of Sorts and Moves apply equally to Excel. There is nothing special in how Numbers handles these situations.

Jerry

Excel does have the provision to sort as you had been doing, sorting one or more columns while leaving others untouched. Numbers does not, it sorts entire rows and there is no provision to leave a column untouched.

Yes, I didn't notice that the OP wasn't sorting entire rows.

Jerry

Hi all, and thanks for the insight all of you have provided. I encountered the same problem that J Edmondson describes at the top of this thread. I too am Excel proficient. As I tried to resolve this problem I studied the iWork help page entitled "Distinguishing Absolute and Relative Cell References." Based on the definitions in that page, I think of the problem with cell references and sorting, as described by J and experienced by me also, as a bug in Numbers. I say this because even though my formulas are written with relative and not absolute references (no dollar signs), when I sort the table the references behave as if they were absolute. I have the identical spreadsheet in Excel and it does not have this problem. My work-around in Numbers is simple: I merely copy the formula from a cell where it is correct (uncorrupted by the sort) and paste it into the cells that I sorted. But if I'm wrong about this being a "bug" I'd like help to correct my misunderstanding of the Numbers concept of absolute and relative references. Thanks, Tom

Tom,

I'll make the case that what you are seeing is not a bug in Numbers. I think you are viewing the situation from a particular perspective that makes sense to you, but you may not be considering equally important alternative scenarios for which the Numbers scheme makes perfect sense. If you call it an incompatibility with Excel, I'd be more likely to agree. Numbers should not be judged on it's fidelity as a clone.

Take the case where we have in column A a list of produce items, and in column B quantities of each item in column A, and somewhere in column C an equation that adds the number of oranges to the number of apples, assuming that both were items included in column A. Now if you sort the list on column A such that the order of the produce list changes, the total of Apples and Oranges remains the same in Numbers, and it changes in Excel. Which is the correct behavior?

Jerry

Jerrold, thanks for your thoughts, which describe a perfectly reasonable perspective. I'll just add that I tried to not judge Numbers as a clone to Excel. Rather, I am trying to understand what I perceived as an incompatibility within Numbers between how a relative reference is defined and how those references behave during sorting. I'll look back at the definitions to see where I've misunderstood.

Thanks, Tom

Tom, it is not a question of relative or absolute references. Those work the exact same way in Numbers as they do in Excel. These two methods of referencing are there so that you can copy formulas from one cell to another and end up with the formula you want. It has no effect on how Excel sorts or on how Numbers sorts.

The way Excel sorts is probably the more useful of the two in many if not most situations but neither is right or wrong. What I would like is the ability to choose which method to sort by.

Very interesting discussion and I'm following with great interest. My issue is very similar. I have multiple sheets that I use to collect 360 degree feedback data. On one sheet I have a list of all the question numbers with the scores for each question. Three columns A=question number, b= question text, c= score.

I want to sort this sheet in descending order by score to get a list from highest score to lowest.

Then on a separate sheet I have a table which is supposed to pull the question text and score from the top 5 and bottom 5 scores.

I've written formulas to simply pull the cell contents from the top 5 and bottom 5, but any time the data changes or gets re-sorted, the formula pulls the questions and scores from the original sort.

Any ideas are greatly appreciated.

t,

You can decouple your top-5/bottom-5 sheet from the effects of sorting the collection sheet you using LOOKUP, LARGE and SMALL functions rather than by referencing particular cells in the collection sheet. If you do that it won't even be necessary to sort to get your result.

Jerry

J Edmondson wrote:
... If you have large amounts of data the way Numbers sorts poses all sorts of issues.

I don't believe that it's any more difficult to fill a large range of cells with an expression that works with the Numbers sort method than with one that doesn't. The important takeaway from this discussion is that if you plan to sort, you must program your references accordingly. This will require using some less common methods, but they aren't difficult to learn. The methods that work in Numbers with a sort will also work in Excel. They are robust and straight forward.

Jerry