Index to define a range
 

Hi,
Last year, I got a very handy tip from Harlan Grove to improve my very
clumsy Indirect(Address(Match...)) statements to define the endpoint of
ranges used for calculations (eg Max etc) by using much simpler, non-volatile
Index statements (thanks Harlan €“ this is still a life saver). Now I am
trying to construct a formula in which I need to define as a function the
start of the range I want to perform a calculation on as well as the end.
Previously, I had something like:
Average(B23:index(B:B,some_calculation_to_define_m y_endpoint)
Is there a (simple) way, avoiding volatiles if at all possible, to use a
similar construction to define the starting cell of the reference (B23 in the
€œexample€� above) too? I (think I) can construct an Offset statement to
achieve the result I want but would prefer something not depending on
volatiles if such a construction is possible.
Many thanks, as always, for any help
Boris.

Index to define a range
 

How about:


=AVERAGE(INDEX(B:B,some_calculation_to_define_my_s tartpoint):INDEX(B:B,
some_calculation_to_define_my_endpoint))

??


In article ,
Boris wrote:

Hi,
Last year, I got a very handy tip from Harlan Grove to improve my very
clumsy Indirect(Address(Match...)) statements to define the endpoint of
ranges used for calculations (eg Max etc) by using much simpler, non-volatile
Index statements (thanks Harlan €“ this is still a life saver). Now I am
trying to construct a formula in which I need to define as a function the
start of the range I want to perform a calculation on as well as the end.
Previously, I had something like:
Average(B23:index(B:B,some_calculation_to_define_m y_endpoint)
Is there a (simple) way, avoiding volatiles if at all possible, to use a
similar construction to define the starting cell of the reference (B23 in the
€œexample€� above) too? I (think I) can construct an Offset statement to
achieve the result I want but would prefer something not depending on
volatiles if such a construction is possible.
Many thanks, as always, for any help
Boris.

Index to define a range
 

Fantastic. Now, I though I had tried (and tried and tried) that format but
kept getting errors and assumed these were to do with the ":" in the middle
of the statement... but it must have been something else in my typing!
Many thanks for your quick and helful response.
Best wishes, Boris.

"JE McGimpsey" wrote:

How about:


=AVERAGE(INDEX(B:B,some_calculation_to_define_my_s tartpoint):INDEX(B:B,
some_calculation_to_define_my_endpoint))

??


In article ,
Boris wrote:

Hi,
Last year, I got a very handy tip from Harlan Grove to improve my very
clumsy Indirect(Address(Match...)) statements to define the endpoint of
ranges used for calculations (eg Max etc) by using much simpler, non-volatile
Index statements (thanks Harlan €€œ this is still a life saver). Now I am
trying to construct a formula in which I need to define as a function the
start of the range I want to perform a calculation on as well as the end.
Previously, I had something like:
Average(B23:index(B:B,some_calculation_to_define_m y_endpoint)
Is there a (simple) way, avoiding volatiles if at all possible, to use a
similar construction to define the starting cell of the reference (B23 in the
€œexample€� above) too? I (think I) can construct an Offset statement to
achieve the result I want but would prefer something not depending on
volatiles if such a construction is possible.
Many thanks, as always, for any help
Boris.