Forum Discussion
How do you reference a cell which tracks being sorted?
This may not be standard spreadsheet theory but, as I see it, there are two important data structures in Excel. The first is the table, which may be sorted or filtered on any field. The second is an array which is an ordered list in which data acquires meaning by virtue of its position. An array cannot be sorted without changing the significance of the data since values are looked up by index.
Assuming we are considering a list object (aka table) then the way one looks up a specific data value is by pairing a foreign key to the primary key in the table. Thus, instead of 3 direct cell references one would use a list of 3 foreign keys to identify the values to be averaged. This can be done with VLOOKUP
= AVERAGE( VLOOKUP( FKey, Table1, 2, FALSE ) )
or, in future, using a vastly superior function XLOOKUP
= AVERAGE( XLOOKUP( FKey, Key, Values ) )
Thanks for the advice. I have read and re-read the VLOOKUP description and am confused.
I have the unique text string to use as a key in column C such as "Diagnostics Services"
I have an integer in column E which is the result to be retrieved.
The table has 74 rows including the one row header.
What should the VLOOKUP statement be to retrieve the integer?
In your provided example below, it seems that there may be a better way to average multiple integers than =AVERAGE( VLOOKUP(params1), VLOOKUP(params2), VLOOKUP(params3))
How might that work?
Thanks in advance, this would be a big help!
Do I understand correctly you sort all columns?
When it could be
=AVERAGEIFS($E$2:$E$33,$C$2:$C$33,$G$7)I want to average particular cells in a column which may be moved around due to sorting.
Content based keys with a table offset seems one way to go.
AFAIK using $ is to fix a cell used in a calculation, despite copy and pasting the formula to other cells. I don't see how this would help when the cells of interest move around.
Do you sort only this one column or you re-arrange few columns based on sorting one? If few columns are re-arranged you may use AVERAGEIFS() instead of AVERAGE() to select cells to average based on criteria, aka key in description column.
