creating intervals and calculating averages
 

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

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:A2020)*(A1:A20<=40),B1:B20))
array entered (Ctrl-Shift-Enter) should do the trick.

Jerry

Nathan D wrote:

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

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
--
Dana DeLouis
Win XP & Office 2003


"Nathan D" <Nathan wrote in message
...
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