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 communityI'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 .
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.
=AVERAGEIF(B1:B9,95,A2:A10)
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.
Try the functions sumif and countif. In your case:
=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.
SUMIF
be offset one row from the range used for the criteria? e.g. SUMIF($B$1:$B$9, 95, $A$2:$A$10)