selective averaging

[Wazooli]

I have a large data set that has a particular column containing identifiers.
Here is an example:

RP11-148D23
RP11-148D23
RP11-148F17
RP11-148F17
RP11-148F17
RP11-148F17
RP11-148G20
RP11-148G20
RP11-148G7
RP11-148G7

As you can see, most of the entries in my sheet are duplicates, but there
are several that are repeats of 4, 6 or 8. Each identifier has a particular
data value assiciated with it. I would like to average across each
identifier, averaging the associated data, but merely pasting down a row does
not handle the replicates that contain more than 2 or the same kind. Is
there a way to do this conveniently? My spreadsheet contains 13000 rows.

 

[CarlosAntenna]

Sounds like a job for a pivot table.

Carlos

 

[Dave R.]

If I understand correctly, you have the list below (say in A1:A13000) with
some numbers next to the identifiers (say B1:B13000), and you want to
calculate an average for an identifier?

If that's all you want to do one way is to use

=AVERAGE(IF(A$1:A$13000=A1,B$1:B$13000))

entered with CTRL-SHIFT-ENTER, then copy down.

 

[Wazooli]

I have not yet ever had any need for array formulas. i will however, give it
a try. Thanks.