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

0
votes

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,

2
excel
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

2 Answers

0
votes

Just use 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:

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

I had similar problem and used this:

=IF(COUNTIF($A$2:$A$18,$A2),AVERAGEIFS($B$2:$B$18,$A$2:$A$18,$A2),"")

IF function with "logical_test" as duplication and "value_if_true" as Average of the values matching the duplicated value and the "value_if_false" as "" to get the single input already existing for the single value.

It worked successfully only that the result repeated for every duplicate value.. but after that you can remove the duplicates.