I'm trying to get the average depending on another cell.
Basically I want the average of all cells where the value of the upper right cell equals 95.
In this example I would like the average of A3, A6, A7, A8, A9 .
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
Your question is tagged with
Excel-2013 so I would suggest the AVERAGEIF function offsetting the optional average_range by one row compared to the range.
While the size of the average_range and average_range have to stay the same, there is nothing stopping you from offsetting one from the other by rows, columns or both.
=SUMIF($B$1:$B$9, "=95", $A$1:$A$9)/COUNTIF($B$1:$B$9, "=95")
If you put 95 in a cell say C1, then
=SUMIF($B$1:$B$9, "=" & $C$1, $A$1:$A$9)/COUNTIF($B$1:$B$9, "=" & $C$1)
Of course you can improve upon this (handle when nothing matches) but this is the main idea.