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?
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.
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.
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 spreadsheets--new 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!
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
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?
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.
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.