Sorting in numbers

Numbers treats rows as records (i.e., as in a database) and the sorting method follows that thinking. If a field in record 1 refers to a field in record 2, it will continue to do so after a sort. If you keep that in mind it all makes sense. Excel doesn't use that methodology. To get Numbers to sort as Excel does takes some simple formulas, though not as simple and certainly not as clear as =D1+C2.

I take back my earlier statement (here and/or in another thread) that relative versus absolute referencing makes no difference in a sort. It does in Excel, not in Numbers. But as far as I can tell, neither referencing method will allow Excel to sort like Numbers does, nor will INDIRECT, OFFSET and the like. Not that this has any bearing on what you want to do, I'm just pointing it out.

badunit,

I believe that if you were to build indirect address references using ROW() and COL() inside ADDRESS functions, that Excel would treat them just as Numbers does in a sorting environment. Haven't tried it, but I can't see why it wouldn't operate the same way in either program. I'm thinking of the rather common application of keeping a running total in a balance sheet or check register as being the most likely subject of such a discussion.

Jerry

J,

I try to refrain from making judgments here. The purpose of this forum is to help users to become proficient with Numbers, not to pick winners in the spreadsheet wars.

Jerry

If everyone followed the "de-facto standard" for spreadsheets, all spreadsheet applications would be exact duplicates of Excel, including the interface and the monolithic tables. If everyone had to match the "de-facto standard", everyone would sit on their hands waiting for Microsoft to make a change so that they could copy it; there would be no independent thinking. It is true that Numbers is not Excel, for better or worse.

Many of us prefer Numbers because it isn't just like Excel. If you want a spreadsheet just like Excel, you'll need to use Excel. You might find one of the Office clones, NeoOffice or Open Office, to be close enough to not notice a difference, otherwise, buy Microsoft Office.

Hi Barry,

Your formula works but it seems to contain more elements <like ROW() and COLUMN()> than the examples given in the user guide: =OFFSET(D7,0,0,3,1).

I created my own formula for your table: =IF(ISBLANK(C),"",OFFSET($A$1,1,3)+OFFSET($A$1,2,2)) and it works for the first row but not for the others.

Can you explain what is the purpose of ROW() and COLUMN() in your formula?

I'm trying to create a formula for a chequing account with a column for credit and seperate column for debit.

Thanks,

Verseau

Verseau1955 wrote:
Hi Barry,
Your formula works but it seems to contain more elements <like ROW() and COLUMN()> than the examples given in the user guide: =OFFSET(D7,0,0,3,1).

I created my own formula for your table: =IF(ISBLANK(C),"",OFFSET($A$1,1,3)+OFFSET($A$1,2,2)) and it works for the first row but not for the others.

Can you explain what is the purpose of ROW() and COLUMN() in your formula?

I'm trying to create a formula for a chequing account with a column for credit and seperate column for debit.

Hi Verseau,

The guide formula is an example of the OFFSET function alone, written in the simplest form possible.
OFFSET can accept up to five arguments, as noted in the syntax model supplied by the Function Browser:

=OFFSET(base,row-offset,column-offset, rows,columns)

The last two (in italics ere,light grey as supplied by the Function browser) are optional, and are not needed in this case.

In your first use of OFFSET:

OFFSET($A$1,1,3)

The base cell is A1, the row-offset tells the formula it is to get a value from a cell one row down from A1 (ie. row 2), the column-offset tells the formula to get the value from a cell 3 columns to the right of A1 (ie. column D). The cell at that location is D2, so the function returns the value 1000 from that cell.

The same part of my formula has OFFSET($A$1,ROW()-2,COLUMN()-1,1,1), but the last two arguments are optional and in this case unnecessary, so lets drop them.

OFFSET($A$1,ROW()-2,COLUMN()-1)

ROW() returns a number that is the number of the row containing the formula.
COLUMN() returns a number that is the number of the column containing the formula.
For the formula in D3:
ROW() returns 3, so ROW()-2 returns 1
COLUMN() returns 4, so COLUMN()-1 returns 3

and OFFSET returns the value from the same cell one row below and three columns to the right of A1 as in your example.

The difference comes when you fill either formula down from D3 through the rest of column D, the fixed reference to A1 ($A$1) will remain the same, as will any function arguments specified as fixed values. That means that when your formula is in D7, OFFSET will still show as OFFSET($A$1,1,3), OFFSET is still looking at the cell one row below and three columns to the right of A1, and again returns the value 1000 from D2.

When the two fixed value arguments (1 and 3) are replaced with the expressions calculating the arguments, the formula still looks the same, and since this copy of the formula is still in the same column, the value returned by COLUMN() will be the same. But the value returned by ROW() is now 7 instead of 3, and the expression ROW()-2 now returns 5. Substituting the calculated values for the expressions, OFFSET is now using OFFSET($A$1,5,3), and is looking at D6, the cell containing the previous balance.

COLUMN() provides the same adjustment if the formula is filled to the right, or if the formula is used in a different column than D. A column offset from column A of COLUMN()-1 will always return a value from the same column as the cell containing the formula.

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



Regards,
Barry