Forum Discussion
How do you reference a cell which tracks being sorted?
I want to calculate the average of multiple cells in a column which can be sorted. If is use = AVERAGE(E33,E25,E26) then sort the column, then my answer changes, which I do not want.
Is there a way to have the formula reference stick to a cell no matter how the column that the cell is in is sorted?
10 Replies
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)
