Office Software Thread, Excel - Averge value of a subset of results in Technical; Calling all Excel gurus (or possibly just anybody with more nouse than I)... Range_1 Range_2 a...............20 b...............10 c...............10 a...............10 c...............30 ...
1. ## Excel - Averge value of a subset of results

Calling all Excel gurus (or possibly just anybody with more nouse than I)...

Range_1 Range_2
a...............20
b...............10
c...............10
a...............10
c...............30
b...............20

I would like a formula that finds the average of results in 'Range_2' for all rows in 'Range_1' that equal "a".

I thought something like:

=AVERAGE(SUMPRODUCT((Range_1="a")*(Range_2))

However, this returns the sumproduct but not average. Any help would be very much welcomed.

Thanks a lot.

Ben

2. =AVERAGE(SUMPRODUCT((Range_1="a")*(Range_2))
OK,

This works:

=SUMPRODUCT((Range_1="A")*(Range_2)/COUNTIF(Range_1,"A")

but is there a more elegant way?

Thanks all,

Ben

SHARE: