
Badunit Sep 19, 2010 10:52 PM
Re: Sorting in numbers in response to J EdmondsonYou 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. 
Barry Sep 19, 2010 11:19 PM
Re: Sorting in numbers in response to J EdmondsonJ 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 resort 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 nonsorting (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 
Jerrold Green1 Sep 20, 2010 12:47 AM
Re: Sorting in numbers in response to J EdmondsonJ,
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 
J Edmondson Sep 20, 2010 7:38 AM
Re: Sorting in numbers in response to Jerrold Green1Thanks for the replies.
Jerrold, I never had this issue with Excel. I had the same spreadsheet on excel and sorted by dates and the formula was a relative reference and it calculated correctly. I was kind of shocked this is how Numbers handled a sort of data.
I will try to modify the formula. 
J Edmondson Sep 20, 2010 7:41 AM
Re: Sorting in numbers in response to BarryBarry,
Yes you are exactly right and is pretty much what my sheet looks like. I am an accountant and not a newby by any stretch to spreadsheetsnew to this program absolutely and to Mac in general. And need I say, I will never go back to Wintel in my home. What a wonderful experience. However, when I was messing around putting in bank transactions I couldn't figure out what the heck was going on. Now I know. Excel doesn't do this because I had the same sheet with same formulas and did the same sort and it did it shall I say the right way. If people aren't aware this is how the function works it can really wreak havoc.
Thanks for the responses...appreciate it! 
Badunit Sep 20, 2010 1:20 PM
Re: Sorting in numbers in response to J EdmondsonExcel 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. 
Jerrold Green1 Sep 20, 2010 5:53 PM
Re: Sorting in numbers in response to BadunitYes, I didn't notice that the OP wasn't sorting entire rows.
Jerry 
TomH47 Sep 28, 2010 3:22 PM
Re: Sorting in numbers in response to J EdmondsonHi 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 workaround 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 
Jerrold Green1 Sep 28, 2010 4:21 PM
Re: Sorting in numbers in response to TomH47Tom,
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 
TomH47 Sep 28, 2010 4:41 PM
Re: Sorting in numbers in response to Jerrold Green1Jerrold, 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 
Badunit Sep 28, 2010 9:09 PM
Re: Sorting in numbers in response to TomH47Tom, 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. 
tsieck Sep 30, 2010 7:09 AM
Re: Sorting in numbers in response to BadunitVery 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 resorted, the formula pulls the questions and scores from the original sort.
Any ideas are greatly appreciated. 
Jerrold Green1 Sep 30, 2010 7:17 AM
Re: Sorting in numbers in response to tsieckt,
You can decouple your top5/bottom5 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 Sep 30, 2010 5:12 PM
Re: Sorting in numbers in response to Jerrold Green1I am pleased my simple query has elicited so many responses. I have to say that Excel's sorting algorithm makes more sense in my mind. If you have large amounts of data the way Numbers sorts poses all sorts of issues.