# Calculate average depending on upper right cell

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 . • What about A1? There can't be any cells offset up to the right from it. Nov 2, 2014 at 1:31

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.

• Shouldn't the sum_range used in your `SUMIF` be offset one row from the range used for the criteria? e.g. `SUMIF(\$B\$1:\$B\$9, 95, \$A\$2:\$A\$10)`
– user385793
Nov 2, 2014 at 12:54
• @Jeeped true but if he really wanted that, he can adjust the formula. Behavior of edge cases should be dealt with by the original poster.
– Peon
Nov 2, 2014 at 22:15