Index to define a range

[Boris]

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_my_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.

 

[JE McGimpsey]

How about:


=AVERAGE(INDEX(B:B,some_calculation_to_define_my_startpoint):INDEX(B:B,
some_calculation_to_define_my_endpoint))

??

 

[Boris]

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.