creating intervals and calculating averages

[Nathan D]

Hello,
I have a set of data looking like this:
distance Deflection
0 1
2 3
5 2
11 7
14 6
19 15
23 8

etc.
so basically the distance column is random and i have data points at these
random distances.

I am trying to average the deflection values over distances of 20 metres or
so.
to end up with something like

distance deflection
0-20 5.67
20-40 24.3

etc.

any help would be great.
Thanks

 

[Jerry W. Lewis]

What do you want to do about endpoints? For example is exactly 20 to be
averaged into 0-20, or 20-40, or both?

Assuming the distances are in A1:A20 and the deflections are in B1:B20,
then something like
=AVERAGE(IF((A1:A20>20)*(A1:A20<=40),B1:B20))
array entered (Ctrl-Shift-Enter) should do the trick.

Jerry

 

[Dana DeLouis]

Don't know if this is the best option, but a Pivot table may be 1 idea.
Select your data, and do Data | Pivot Table...
In the layout wizard, drag distance to the Row field.
Drag deflection to the data area.
Double click "Sum of Deflection" and change it to Average.
Click Ok and finish.
At this point, you will have a list of all your data points.
Now, you want to "Group" your data into groups of 20.
Right Click anywhere in your Distance column and select "Group and Show
detail", and select "Group"
Make sure it starts at 0, and for "By:", enter 20.
I believe the row displayed as 0-20 is the average of the values from 0 up
to but not including 20.
The next row (20-40) is the average from 20 up to but not including 40.
HTH