Finding an Average of every 25 rows.

[rtb1991]

Hi there, ive been given a large data set for a uni project which is too large to be useful at the minute. It consists of about 30,000 rows of element abundances for every 0.2mm of a sediment core. A sample of the file is attatched.

I would like to find the average of all the elements for every 5mm (so every 25 rows) of the sediment core with out having to go through every row individually finding an average. Ideally the average would be plotted against the medial depth of the values averaged i.e for the first 5mm the average would be plotted against 50.25cm.
Sorry if ive not given enough info (if not just let me know). Any help would be very much appreciated.

[Pepe Le Mokko]

Members aren't usually keen on downloading sheets from external sites, so best attach the sample sheet to your post ( see FAQ for procedure)

[FDibbins]

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[rtb1991]

Ah ok, cheers for letting me know. I tried attaching it but the file is too large.

[Pete_UK]

Well we only need a small sample of the file, say 100 rows, so delete all the other rows, save it with a different name, and then attach that workbook.

Hope this helps.

Pete

[rtb1991]

Cool, done that, cheers.

[Pete_UK]

Okay, just copy the top row from Sheet2 into Sheet3, so that you have the same headings, and then you can put this formula in C2 of Sheet3:

Please Login or Register  to view this content.

This will work out the average of column C from rows 2 to 26 inclusive. The formula can be copied across into D2:AD2, and it will work out the average of those 25 rows for the appropriate column. That block of formulae in C2:AD2 can then be copied down as far as you need it, and it will take the average of the next 25 rows successively, i.e, 27 to 51, 52 to 76, 77 to 101 etc.

I'm not sure what you would want in columns A and B - probably the maximum of each 25 row range. If that is the case, then copy the formula from C2 into A2:B2, and then just change the word AVERAGE to MAX at the start of each formula, and then copy those down.

I haven't a clue what the values in AE and AF represent, so I'm not sure what to do with those.

Hope this helps.

Pete

[rtb1991]

Thank you! That works perfectly. I have one other question. The actual spreadsheet (as opposed to the sample) has named sheets (i.e instead of "sheet1/2" ect, they are named "BARD2 Dr A 0.5-1 m" or BARD2 Dr A 1-2 m" ect. How would that be substituted for the "sheet2" in the equation? Would I include the spaces and would i include the exclamation mark?

[Pete_UK]

Yes, the exclamation mark separates the sheet name from the cell references, so needs to be there, and you must include the full name of the sheet(even if it has trailing spaces). If there are spaces in the name, then you must also include apostrophes around the name, like this:

=AVERAGE(INDEX('BARD2 Dr A 0.5-1 m'!C:C,(ROWS($1:1)-1)*25+2):INDEX('BARD2 Dr A 0.5-1 m'!C:C,(ROWS($1:1))*25+1))

Hope this helps.

Pete

P.S. Please mark the thread as Solved if you consider it to be so (the FAQ describes how).

[rtb1991]

Thanks for the help Pete, i'll give that a shot and mark it as solved if i get it sorted.