1

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 .

enter image description here

Improve this question
1
  • What about A1? There can't be any cells offset up to the right from it.
    – Raystafarian
    Nov 2, 2014 at 1:31

2 Answers 2

Reset to default
1

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)

     AVERAGEIF with offset

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.

Improve this answer
1

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.

Improve this answer
2
  • 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .