average values and reduce and simplify the worksheet

O

Omics

I have a Excel2003 worksheet containing 2 columns and more than 20,000 rows.
Column “B†lists the names of objects and column “A†lists the values of
repeated measurements for each object. The repeating times of measurement
vary from object to object. I will need to average the measurement values for
each object, and reduce the table to one object with one average value. Could
somebody tell me how to do this? Thanks.

Example:
From:
A B
1.8099 aa
1.8685 aa
1.8081 aa
2.0376 aa
1.4996 aa
1.984 aa
0.4723 bb
0.3107 bb
0.4174 bb
0.2281 cc
0.3621 cc
0.3618 cc
0.1087 cc
0.2929 cc
2.5983 dd
2.1616 dd


To:
1.8346 aa
0.4001 bb
0.2707 cc
2.38 dd
 
J

jlclyde

I have a Excel2003 worksheet containing 2 columns and more than 20,000 rows.
Column “B” lists the names of objects and column “A” lists the values of
repeated measurements for each object. The repeating times of measurement
vary from object to object. I will need to average the measurement values for
each object, and reduce the table to one object with one average value. Could
somebody tell me how to do this? Thanks.

Example:
From:
A       B
1.8099  aa
1.8685  aa
1.8081  aa
2.0376  aa
1.4996  aa
1.984   aa
0.4723  bb
0.3107  bb
0.4174  bb
0.2281  cc
0.3621  cc
0.3618  cc
0.1087  cc
0.2929  cc
2.5983  dd
2.1616  dd

To:
1.8346  aa
0.4001  bb
0.2707  cc
2.38    dd
Click to expand...
Omics,
You can do this with code, but here is a non code way. Highlight all
of the aa's bb's and so on. Click on Data/Filter.../Advanced Auto
Filter. Click on copy to another location. Choose said location.
then select uniques values. Then next to this new column of data add
this formula =SUMIF(B$2:B$17,E2,A$2:A$17)/COUNTIF(B$2:B$17,E2) If the
data is in a as numbers and B for what to lookup. Copy this down to
all cells. Then you can copy and paste special/ Values and then you
could delete the old stuff. If you want.

Thanks,
Jay
 
B

Bob Phillips

You can use a simpler array formula

=AVERAGE(IF(B2:B17=E2,A2:A17))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



I have a Excel2003 worksheet containing 2 columns and more than 20,000
rows.
Column “B” lists the names of objects and column “A” lists the values of
repeated measurements for each object. The repeating times of measurement
vary from object to object. I will need to average the measurement values
for
each object, and reduce the table to one object with one average value.
Could
somebody tell me how to do this? Thanks.

Example:
From:
A B
1.8099 aa
1.8685 aa
1.8081 aa
2.0376 aa
1.4996 aa
1.984 aa
0.4723 bb
0.3107 bb
0.4174 bb
0.2281 cc
0.3621 cc
0.3618 cc
0.1087 cc
0.2929 cc
2.5983 dd
2.1616 dd

To:
1.8346 aa
0.4001 bb
0.2707 cc
2.38 dd
Click to expand...
Omics,
You can do this with code, but here is a non code way. Highlight all
of the aa's bb's and so on. Click on Data/Filter.../Advanced Auto
Filter. Click on copy to another location. Choose said location.
then select uniques values. Then next to this new column of data add
this formula =SUMIF(B$2:B$17,E2,A$2:A$17)/COUNTIF(B$2:B$17,E2) If the
data is in a as numbers and B for what to lookup. Copy this down to
all cells. Then you can copy and paste special/ Values and then you
could delete the old stuff. If you want.

Thanks,
Jay
 
Top