Averages from each of multiple sheets

[tontondavid10]

I have a workbook with 100+ sheets. Each sheet represent a time point. I would like to plot the averages of the values for each time point. This clearly doesn't work by just expanding the table, so is there a solution to this problem?

Secondly, each time point sheet is actually a table of distances between a series of points and every other point in the series. This means I have a table with duplicate values since distances a>b = b>a. So I wanted to calculate the averages for columns of decreasing lengths (A1:A100,B2:B100, C3:C100 ...). Is there an easy way to create this formula in the spreadsheet that doesn't involve manually selecting each column in turn?

Thanks for the help.

David

 

[pbornemeier]

What is the layout of data on each sheet?
What do you mean by "expanding the table"?
A1:A100,B2:B100, C3:C100 ... all have the same "length" (100 rows), are you speaking of the contents of the cells?

If you want the average of the same cell (C4 in this example) in each of the worksheets in the workbook, Name the leftmost sheet "First" and the rightmost sheet "Last" and use this formula: =AVERAGE(First:Last!C4). When adding new sheets insert them between sheets First & Last.

I do not understand what you are asking for in the 2nd paragraph. Please restate.

 

[tontondavid10]

The table layout is as follows:
[TABLE="width: 568"]
<!--StartFragment--> <colgroup><col width="71" span="8" style="width:53pt"> </colgroup><tbody>[TR]
[TD="width: 71"][/TD]
[TD="width: 71"]2852[/TD]
[TD="width: 71"]2853[/TD]
[TD="width: 71"]2854[/TD]
[TD="width: 71"]2855[/TD]
[TD="width: 71"]2856[/TD]
[TD="width: 71"]2857[/TD]
[TD="width: 71"]2859[/TD]
[/TR]
[TR]
[TD]2851[/TD]
[TD="align: right"]1.02862E-05[/TD]
[TD="align: right"]7.97471E-05[/TD]
[TD="align: right"]2.37859E-05[/TD]
[TD="align: right"]9.02477E-06[/TD]
[TD="align: right"]1.40836E-05[/TD]
[TD="align: right"]5.52129E-05[/TD]
[TD="align: right"]4.64055E-05[/TD]
[/TR]
[TR]
[TD]2852[/TD]
[TD][/TD]
[TD="align: right"]7.43481E-05[/TD]
[TD="align: right"]1.571E-05[/TD]
[TD="align: right"]1.55673E-05[/TD]
[TD="align: right"]8.58378E-06[/TD]
[TD="align: right"]5.01939E-05[/TD]
[TD="align: right"]3.95187E-05[/TD]
[/TR]
[TR]
[TD]2853[/TD]
[TD="align: right"]7.43481E-05[/TD]
[TD][/TD]
[TD="align: right"]8.0449E-05[/TD]
[TD="align: right"]8.81326E-05[/TD]
[TD="align: right"]8.15646E-05[/TD]
[TD="align: right"]2.55246E-05[/TD]
[TD="align: right"]9.77957E-05[/TD]
[/TR]
[TR]
[TD]2854[/TD]
[TD="align: right"]1.571E-05[/TD]
[TD="align: right"]8.0449E-05[/TD]
[TD][/TD]
[TD="align: right"]2.4475E-05[/TD]
[TD="align: right"]1.08643E-05[/TD]
[TD="align: right"]5.81721E-05[/TD]
[TD="align: right"]2.47994E-05[/TD]
[/TR]
[TR]
[TD]2855[/TD]
[TD="align: right"]1.55673E-05[/TD]
[TD="align: right"]8.81326E-05[/TD]
[TD="align: right"]2.4475E-05[/TD]
[TD][/TD]
[TD="align: right"]1.40434E-05[/TD]
[TD="align: right"]6.33877E-05[/TD]
[TD="align: right"]4.35886E-05[/TD]
[/TR]
[TR]
[TD]2856[/TD]
[TD="align: right"]8.58378E-06[/TD]
[TD="align: right"]8.15646E-05[/TD]
[TD="align: right"]1.08643E-05[/TD]
[TD="align: right"]1.40434E-05[/TD]
[TD][/TD]
[TD="align: right"]5.7675E-05[/TD]
[TD="align: right"]3.24515E-05[/TD]
[/TR]
[TR]
[TD]2857[/TD]
[TD="align: right"]5.01939E-05[/TD]
[TD="align: right"]2.55246E-05[/TD]
[TD="align: right"]5.81721E-05[/TD]
[TD="align: right"]6.33877E-05[/TD]
[TD="align: right"]5.7675E-05[/TD]
[TD][/TD]
[TD="align: right"]7.74635E-05[/TD]
[/TR]
[TR]
[TD]2859[/TD]
[TD="align: right"]3.95187E-05[/TD]
[TD="align: right"]9.77957E-05[/TD]
[TD="align: right"]2.47994E-05[/TD]
[TD="align: right"]4.35886E-05[/TD]
[TD="align: right"]3.24515E-05[/TD]
[TD="align: right"]7.74635E-05[/TD]
[TD][/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

The first row and columns contain the object ID, the cells at the intersections of each row and column contain the distance. So as you see, the distance between Object 2852 and 2853 is the same as that between 2853 and 2852. That's not so much of a problem as it only really affects the StDev. The bigger problem is that there are a different number of objects in each table, and each table is in a separate sheet.

 

[pbornemeier]

For the data you gave in the last post (that occupies A1:H9), use this formula in B11:
=AVERAGE(INDIRECT("R"&COLUMN()+2&"C:R9C",FALSE))
and drag copy it to G11. It gives an average for the part of each column below the empty cell to the last filled cell in the column.
If the real data extends to row 100 and the blank cell in column B is still at row 3, the next formula should work for B102 and drag-copied right as far as needed:
=AVERAGE(INDIRECT("R"&COLUMN()+2&"C:R100C",FALSE))

I am unclear about how you want to combine the data from different worksheets.