Excel: Find average in a range that depends on the duplicate values in another column

I want to calculate the average of a range of cell entries depending on specific criteria. The criteria is: the range in which column A has the same value, I need to calculate the average in that range, but from the data entered in column B

In the attached figure-

A9:A10 has the same value (=4)

Therefore C9=average(B9:B10)

Similarly,

A11:A15 has the same value (=7)

Therefore C11=average(B11:B15)

enter image description here

Thanks in advance,

Depending on what you are using the results for, is this not a really simple pivot table? if you made a pivot table out of your data, then added "Data 1" to the rows, and "Average of Data 2" to the values, then you would get the results, it would just look different to your example.
– Richard Hansell
Aug 22 at 12:58

1 Answer
1

Just use AVERAGEIF():

AVERAGEIF()

You can put this in the specific cells where you want the average:

=AVERAGEIF($A$2:$A$18,A2,$B$2:$B$18)

Or, put this in B2 and drag down:

B2

=IF($A2=$A1,"",AVERAGEIFS($B$2:$B$10,$A$2:$A$10,$A2))

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

搜尋此網誌

Dfyjkt