|
selective averaging
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. |
Sounds like a job for a pivot table.
Carlos "Wazooli" wrote in message ... 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. |
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" wrote in message ... 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. |
I have not yet ever had any need for array formulas. i will however, give it
a try. Thanks. "Dave R." wrote: 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" wrote in message ... 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. |
| All times are GMT +1. The time now is 11:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com