Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
average values and reduce and simplify the worksheet
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
average values and reduce and simplify the worksheet
On May 22, 12:03*am, Omics wrote:
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 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
average values and reduce and simplify the worksheet
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) "jlclyde" wrote in message ... On May 22, 12:03 am, Omics wrote: 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I reduce the range of a saved worksheet? | Excel Discussion (Misc queries) | |||
How do I reduce the no. of rows & columns in a worksheet ? | Excel Discussion (Misc queries) | |||
simplify procedure to get rid of 0 error values in a spreadsheet | Excel Worksheet Functions | |||
HOW DO I REDUCE THE SIZE OF A WORKSHEET TO THE CURRENT USED CELLS | Excel Worksheet Functions | |||
How do I reduce the worksheet size ? | Excel Discussion (Misc queries) |