Indirect Cell Reference

  • Thread starter Thread starter John Gilchrist
  • Start date Start date
J

John Gilchrist

I want to find the maximum value in a variable-length array

Currently, I use folloiwng to find max value of 1000 - element array

=MAX(C1:C10000)

However, only the first 1500 of these cells are populated, and I wish to
exclude the last two populated cells from the MAX expression

Cell E1 contains the count (1500)
E1 = 1500
Cell E2 contains the # cells to exclude
E2 = 2

How do I use E1 and E2 references in my MAX expression to obtain an
equivalent to the desired expression
=MAX(C1:C1498)

Thank you,
John
 
try this where you are trying to match a number larger than possible in your
column. It will find the row of the last populated less two...

=MAX(INDIRECT("a2:a"&MATCH(99999999,A:A)-2))

If you have blanks before this may not give you what you want.
 
Back
Top