Forum Discussion

alterkaker's avatar
alterkaker
Copper Contributor
Dec 27, 2019

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 w...
excel
alterkaker's avatar
alterkaker
Copper Contributor
Jan 03, 2020

OK, so I got it working, and wonder if there is a way to save on typing the same params so many times?

I have to apply this to many cells

 

=AVERAGE(VLOOKUP("Autonomous Checkout",$C$2:$G$74,3,false), vlookup("Shelf Inventory Management",$C$2:$G$74,3,false), vlookup("Shopper Intelligence",$C$2:$G$74,3,false))

 

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 03, 2020

alterkaker 

Rather than using 3 separate VLOOKUPS, I set the formula up with one function call but an array of 3 input parameters to look for.  My normal way of working would be to use

= AVERAGE( VLOOKUP( fKey, Database, 3, FALSE ) )

but something closer to your working practices, with its direct cell references, might be

 

= AVERAGE(
    VLOOKUP( 
       {"Autonomous Checkout","Shelf Inventory Management","Shopper Intelligence"},
        $C$2:$G$74,
        3,
        FALSE)
    )

 

 In your current formula the average is conducted over 3 parameters (each one a VLOOKUP) whilst in the ones I have put forward, it is conducted over one array resulting from a VLOOKUP with 3 values.  The input values can be a constant array of text strings, as shown, or a reference 'fKey' to a range.

Resources