Averaging data in one column based on an interval in another

[jack.elvy]

Hi,

I would appreciate a VBA code (or excel functions if they are easy) that will average the data in columns B-H based on the interval of 0.00-0.50m in column A.

An example of how the processed data should appear has been done in Columns J-Q.

I am looking to process over 100 spreadsheets in a similar manner, so creating a pivot table for each individual spreadsheet is not ideal.

Cheers

[immigrated4urjob]

This can be done with Pivot Table. Use col A as rows, and divide that into intervals, and set Value to average.

[Pete_UK]

Your ranges cover:

B2:B52
B53:B102
B103:B152

For consistency, shouldn't the first range be:

B3:B52

Also, in cell O2 you have this:

=MEDIAN(F2:F50)

which is inconsistent with the other ranges.

Pete

[jack.elvy]

Thanks for the replies.

I could do it with a pivot table however I am looking to apply a code or simply copy paste formulas over to apply this to voer 100 spreadsheets to process them in a similar manner.

I should have mentioned this previously!

As for the inconsistency, it is because I am counting the 0.00m as being part of the first 0.5m interval, it does not have a significant impact on the rest of the data.

[Pete_UK]

You can use this formula in J2:

=INDEX(A:A,ROWS($1:1)*50+2)

and this one in K2:

=MEDIAN(INDEX(B:B,(ROWS($1:1)-1)*50+3):INDEX(B:B,ROWS($1:1)*50+2))

Copy K2 across to Q2, then you can copy the block of formulae in J2:Q2 down as far as you need to.

Note that these use consistent ranges, i.e. rows 3 to 52, rows 53 to 102, rows 103 to 152 and so on, so the results on the first row are slightly different than yours. If you want then to be exactly the same, you can use this in K2:

=MEDIAN(INDEX(B:B,(ROWS($1:1)-1)*50+IF(ROWS($1:1)=1,2,3)):INDEX(B:B,ROWS($1:1)*50+2))

with the changes shown in red. Copy across and down as before.

Hope this helps.

Pete

[jack.elvy]

Thanks for that Pete, however I am getting 'there is a problem with this formula' for both J2 and K2.

[Pete_UK]

You must have typed them in incorrectly. I've attached the file that I worked on this morning. I put the formulae further to the right starting in column S, so that you can compare the results directly with your own values.

Hope this helps.

Pete