Need to get Address() to Average Function Range argument

[Tweedy]

Using XL2002. . .

I have data in column A that can vary but will always be in ascending order.
(2048 time slices from data acquistions and a header row) I want to be able
to maintain the location of several points used in formulas throughout the
spreadsheet. For instance, one point I need to track is where time zero
is(or the next largest point). So if the points are shifted up or down, the
formula will automatically adjust and find time zero.

So my base static formulas are as such >>> "=AVERAGE(A775:A1275)"

I am able to find the time zero point using >>>
=(ADDRESS(MATCH(0.0,A1:A2049,1)+1,1)) which yields "$A$775"

A similar formula gives me "$A$1275" which is the end of the range I need to
average.

Because these are literal strings they are not accepted as arguments to
AVERAGE.
What can I do to get the two strings and the ":" to fullfill the arguments
needed for AVERAGE?

Thanks for your help.

 

[T. Valko]

A similar formula gives me "$A$1275" which is the

end of the range I need to average.

What's your criteria for defining that as the end of the range?

You can do something like this (assuming there are no empty cells within the
rage):

=AVERAGE(INDEX(A1:A2049,MATCH(0,A1:A2049,0)):"criteria that defines the end
of range")

 

[Tweedy]

Biff,

Criteria for end of range is the same as for the beginning except MATCH
looks for a larger value than the start value. So in my example, the first
value to find is 0 (seconds) and the next can be 0.1(seconds)
--
Ray Tweedale
All-around-nice-guy

A similar formula gives me "$A$1275" which is the
end of the range I need to average.

What's your criteria for defining that as the end of the range?

You can do something like this (assuming there are no empty cells within the
rage):

=AVERAGE(INDEX(A1:A2049,MATCH(0,A1:A2049,0)):"criteria that defines the end
of range")

 

[Tweedy]

Biff,

Thanks for your help. Between my poor vision and poorly written MS XL Help
file I missed a key point in the explanation for INDEX. The formual I ended
up with is

=AVERAGE(INDEX(A12049,MATCH(0,A1:A2049,1),3):INDEX(A12049,MATCH(0.1,A1:A2049,1),3))

This finds Time 0 and then Time 0.1 and provides address for the data range
in column 3. So ultimately with your help I was able to solve the last step
in figuring out how to do this by including the whole cell range. For the
time range found I need to find the associated values for VOLTS, AMPS and
TORQUE and do the average on them as well as some other functions. I
purpsoely left this part out in my initial post so I could keep it simple for
my feeble brain to comprehend.
Thanks again for your help.
--
Ray Tweedale
All-around-nice-guy

A similar formula gives me "$A$1275" which is the
end of the range I need to average.

What's your criteria for defining that as the end of the range?

You can do something like this (assuming there are no empty cells within the
rage):

=AVERAGE(INDEX(A1:A2049,MATCH(0,A1:A2049,0)):"criteria that defines the end
of range")

 

[Glenn]

Using XL2002. . .

I have data in column A that can vary but will always be in ascending order.
(2048 time slices from data acquistions and a header row) I want to be able
to maintain the location of several points used in formulas throughout the
spreadsheet. For instance, one point I need to track is where time zero
is(or the next largest point). So if the points are shifted up or down, the
formula will automatically adjust and find time zero.

So my base static formulas are as such >>> "=AVERAGE(A775:A1275)"

I am able to find the time zero point using >>>
=(ADDRESS(MATCH(0.0,A1:A2049,1)+1,1)) which yields "$A$775"

A similar formula gives me "$A$1275" which is the end of the range I need to
average.

Because these are literal strings they are not accepted as arguments to
AVERAGE.
What can I do to get the two strings and the ":" to fullfill the arguments
needed for AVERAGE?

Thanks for your help.

You need to use the INDIRECT() function.

 

[T. Valko]

Ok, but this seems a little strange to me! I would think you're wanting to
average a different column on these criteria in column A.

Try this array formula** :

=AVERAGE(INDEX(A1:A2049,MATCH(0,A1:A2049,0)):INDEX(A1:A2049,MATCH(TRUE,A1:A2049>0,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

You could do it using your address formulas but the above is a more
efficient method. With your address formulas:

=AVERAGE(INDIRECT(start_formula&":"&end_formula))

--
Biff
Microsoft Excel MVP

Biff,

Criteria for end of range is the same as for the beginning except MATCH
looks for a larger value than the start value. So in my example, the
first
value to find is 0 (seconds) and the next can be 0.1(seconds)

 

[T. Valko]

OK, good deal!

I temporarily lost my internet connection so there was a delay in sending my
latest reply.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Biff,

Thanks for your help. Between my poor vision and poorly written MS XL
Help
file I missed a key point in the explanation for INDEX. The formual I
ended
up with is

=AVERAGE(INDEX(A12049,MATCH(0,A1:A2049,1),3):INDEX(A12049,MATCH(0.1,A1:A2049,1),3))

This finds Time 0 and then Time 0.1 and provides address for the data
range
in column 3. So ultimately with your help I was able to solve the last
step
in figuring out how to do this by including the whole cell range. For the
time range found I need to find the associated values for VOLTS, AMPS and
TORQUE and do the average on them as well as some other functions. I
purpsoely left this part out in my initial post so I could keep it simple
for
my feeble brain to comprehend.
Thanks again for your help.