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 w...
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))
Another variant is
=AVERAGE(AVERAGEIF($C$2:$C$74,{"Autonomous Checkout","Shelf Inventory Management","Shopper Intelligence"},$E$2:$E$74))or if you put above text constants in some helper cells, let say N1:N3, it will be
=AVERAGE(AVERAGEIF($C$2:$C$17,$N$1:$N$3,$E$2:$E$17))One more variant is
=AVERAGE(IF(ISNUMBER(MATCH($C$2:$C$74,$N$1:$N$3,0)),$E$2:$E$74))The latest is an array formula (entered by Ctrl+Shift+Enter)