Averaging data from a range calculated using cell reference number

[Feolet20]

This is a little confusing, so I'll try to explain. We need to average sets
of data from a larger set. We want to take the total length of the data and
divide it into 10. This gives us a section length that will change depending
on how long our data set is. These sections will have a different range, and
we want to average the range on each section.

For example.

If our total length is 10500, with the start length at 6900 and we divide
this into 10 sections, our sec length is 10500 - 6900 / 10 = 360 ft sections.
We want to divide our data:

Depth Value
10 106
11 102
12 120
..
..
..

into these 360 feet sections. This means we'll average our Range 1
(reference B1000 - B740). How do we set up the formulas to divide these
out? We've got to where we can type in the total length and get our cell
reference #'s for each section, but how do we do an averaging formula that
uses those cell references we calculated?

Any help would be greatly appreciated

 

[Bernard Liengme]

Sorry but I am confused. When you say "We need to average sets of data from
a larger set. We want to take the total length of the data and divide it
into 10" I think about say 1000 cells as a "large set of data" But you go on
to talk about 360 FEET. And what is "start length"?

I think we need a clearer explanation of what you want to do - maybe with a
small sample data set.
best wishes

 

[Feolet20]

For Example:

We have a 1 set of data with a depth range of 6900-10500. Each depth has a
measured value associated with it.

Depth Measurement
6900 120
6901 105
6902 115
..
..
..

We want to divide the total length of each data set into 10 equal sections.
Then we want the average of the measured value for each section (1-10).
Thats where the 360 comes in. Each section equals 360 ft.

Every data set has a different total length, and so different section
lengths. We need a formula so that we can copy all of the data in, and not
have to manually enter the cell ranges for the sections.

Make more sense?

 

[Bernard Liengme]

In A1:B20 I have this data
50 10
60 20
70 30
80 40
90 50
100 60
110 70
120 80
130 90
140 100
150 110
160 120
170 130
180 140
190 150
200 160
210 170
220 180
230 190
240 200

There are 20 data points. If I what 10 sections, each section has two data
points
I get the value 2 using =COUNT(A:A)/10 so it should work with a bigger
dataset


In D110 I have section number 1,2,3,...10
In E1:10 I average the B values in every section
1 15
2 35
3 55
4 75
5 95
6 115
7 135
8 155
9 175
10 195

The formula in E1 is:
=AVERAGE(OFFSET($B$1,(D1-1)*COUNT(A:A)/10,0,COUNT(A:A)/10))

Now I have assumed you have equal increments in column A. If this is not the
case we need to record using
=(MAX(A:A) - MIN(A:A))/ 10 to get our section size

I expect other newsgroup readers are bored with this by now so feel free to
communicate privately
best wishes