Using values to create "from:to" formula's

**shine** · 06-25-2009, 10:31 AM

Okay, the topic title is a bit difficult to understand, but I my english lacks a bit I suppose. I'll make the intro and essence of the question. I think the essence whould be enough, but I want to make the question as clear as possible.

[B]Intro:[B]
The problem is the following:

We have gathered a lot of data which for a geological research. We used a lot of hightech gear to gather this data in high resolution and we found a problem in a specific branch of the subject, something that hasn't been seen in the last 20 years or so. Basically a specific type of strength profile was always though to be strictly within a layer and with hard borders to the next layer. We found that this is usually not true and the borders are more large transitionzones. We now want to describe these zones as individual layers.

Because of our used technique, we want to show this problem by displaying the standard deviation. A type of rock that has a constant strength shows a low value, a transitionzone a high value. However, to do all this a lot of manual labor has to be done, since defining a the strength layers/zones are done manually. These numbers are written down in an Excel sheet:

Problem
The Excel sheet has a few collums:
A: Height
B: Strenght at that height

Then we have a summary block
H: From (which is a number that indicates the first line that we want to use)
I: To (which is the last number we want to use.)

We want to create collum C: The average strength for a selection of collum B displayed in every row of that section in collum C.
For example:
Collum B has 236 values and we want to create averages of the rows 2-30, 31-50, 51-236. In H we have below eachother 2, 31, 51 and in I we have 30, 50, 236. (in reality we have much more " bins").

Now I want excell to use those values to run a macro. The effective formula in C should be something like: AVERAGE (B"value in box H1" :B"Value in box I1"). With a lot of pondering this is essentially my only problem: to create a formula where excel reads the number in the box and uses that to complete the "from:to" limits.