How calcuate a cell address

  • Thread starter Thread starter biocopper
  • Start date Start date
B

biocopper

I want to access a cell between the posion of the maximum value in a
array and the end of the array:

I find the max value from =MAX(B1:B500),

then I search for a value (max/2) that lies between cell in the arra
that MAX was located and the end of the array.
=MATCH(Max(B1:B500)/2,Where_Max_Is:B500,-1)

So, I need to be able to compute the cell address where I located MA
to find MAX/2. This is a near Gaussian shape, so MAX/2 exists on bot
sides of the MAX location. I want the row number of the MAX/2 valu
after the MAX location.

As a part of this is it possible to construct a cell address:

=B"Construct_the_row_number_from_a_formula"

so that the construction could come from "INDEX" etc.

How can I construct a cell address - it seems only written addresse
work

Thanks
 
I think you are asking to find the max, then look in the
range after the max, finding the max of that remaining
range.

I worked up a short example based on just 20 rows...

This formula seems to work:


=MAX(INDIRECT(ADDRESS(MATCH(MAX(A1:A20),A1:A20,0)+1,1)
&":A20"))
 
Back
Top