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.