I have a table with 21 rows. A formula in row 21 refers to 3 cells above it. When I sort the column, the formula in row 21 does not refer to the new position of the cells it was pointing to before the sort. In other words, my formula in row 21 was pointing to say "A1+A8-A14". After the sort the formula is the same but of course the values are now different. Is there a work around?
HI Gene,
Not able to test this on Numbers 3, but you might try revising your formula to =A$4+A$8+A$14
Regards,
Barry
That did not seem to do it for me.
Numbers 3 has adopted the same sort behavior as Excel.
I'm curious why you would structure your document that way. With the ease of breaking things into separate tables why would you have a table with a formula that refers to three separate cells in a column?
SG
Hello
The sort behaviour on references has been changed since Numbers v3. If You want the behaviour of Numbers v1 and v2, you need to introduce static index which moves along with row by sort operation and use the index to reference the cell.
E.g.,
Table 1 (excerpt) A22 =A2+A9+A15 B22 =INDEX(B,MATCH(2,C,0)) +INDEX(B,MATCH(9,C,0)) +INDEX(B,MATCH(15,C,0))
Notes.
Column C holds the static indices.
Row 22 is footer row.
A22 and B22 should yield the same result in Numbers v1 and v2 even after the table is sorted by column A whereas they should yield different results in Numbers v3 and B22 is what you're looking for.
Table is built with Numbers v2.
Regards,
H
PS. For your specific case, you might also use something like the following in B22.
B22 =SUMIF(C,2,B) +SUMIF(C,9,B) +SUMIF(C,15,B)
EDIT: Added PS.
Hi MD_Gene,
I am curious along with SG. There may be a more powereful way to organize your table. Also, what is the purpose of the sort? The ease in Numbers of creating report tables has dramatically decreased my need to sort. My report tables present the data sorted as I need it.
quinn
Okay, My table is a list of expenses. I want to sort them every now and then highest to lowest. That's why I need to sort. And my bottom row is doing a calculation against 3 items but when they are sorted the wrong cells are referenced. I see the work arounds described above. I DO NOT know what a "Report Table" is. Thanks
MD_Gene wrote:
I DO NOT know what a "Report Table" is.
Just look at the templates at File > New. The Personal Budget template is a great example of using a "report table" to pull data from a transactions table. Doing a calculation on three items that might get sorted doesn't sound to me like an efficient use of the power of a spreadsheet.🙂 The templates should give you ideas ....
SG
Hi MD_Gene,
In order to continue doing your calculation on the three items within your column you need to find a "sort safe" way of referencing them. This is what Hiroto accomplishes with his index column. If the three items all have a similar label you could use that with SUMIF().
I still don't understand why you are summing those 3 items. Are they items that would be appropriate in the footer?
A report table woud be a seperate table, perhaps on a seperate sheet that extracts data from your expenses table and organizes it how you would like to see it without the need for sorting.
quinn
Hello
If I understand your case correctly, you may simply introduce a mark column which specifies whether the corresponding row is to be summed and use SUMIF() with condition on the mark value. Like this.
Table 1 (excerpt) A22 =A2+A9+A15 B22 =SUMIF(C,1,B)
The formula in B22 is sort-safe.
Regards,
H
Thanks. I see the New template. My expense document is similar but a lot larger. The expenses in my sheet are gross expenses and 3 of them overlap. I have a row with gross expenses. But in order remove the overlap (redundancy) I need to add 1 number (row, subtract 2 other rows, then add a constant). That's why I need to do the calculation.
But I can use vlookup or the other solutions presented here since the rows are labeled and numbered.
Thanks again for all of your help. I am enjoying learning Numbers after 1000 years on Excel and VB.
Does anyone know where I can read about Headers and Footers in Numbers? I tried searching on it Apple's lame help and found almost nothing.
Like I said before I was pretty good in Excel. In Excel the cell references would change in order to keep the calculation consistent before and after the sort. So I am facing a learning curve here. Thanks for all of the insights.
MD_Gene wrote:
Like I said before I was pretty good in Excel. In Excel the cell references would change in order to keep the calculation consistent before and after the sort.
I still use Excel a lot. I'm happy that Numbers 3 now has the sort behavior of Excel. I don't believe you are correct in saying that the behavior is different in the two apps. (It was different in Numbers 2, though).
But in order remove the overlap (redundancy) I need to add 1 number (row, subtract 2 other rows, then add a constant).
That sounds pretty convoluted and confusing! Surely there is a more straightforward way to organize your data in a document.🙂 Without a screenshot and more specifics, though, it is hard to know what to suggest.
SG
formulas do not respond to a sort?
