Find Max from Variable Range

[Dan]

I have the following spreadsheet and want to find the maximum number in a
variable range based on the value of a cell - 3 rows.


A1 B1

45 7
66
43
22
11
22


In the above data I would like to find the max of A4:A7 because 7 is entered
into the cell of A3B3 (7 - 3). The value of A3B3 will change so the range I
need to have max of will change also.

Thanks in advance - Dan

 

[T. Valko]

I'm assuming that you'll be entering valid numbers in B3. For example, based
on your sample if you enter a number <4 in B3 then you'll get an error. So,
with that assumption:

=IF(B3="","",MAX(INDIRECT("A"&B3-3&":A"&B3)))

 

[JMB]

Another way you could do it
=IF(B3>3,MAX(INDEX(A:A, B3-3):INDEX(A:A, B3)),"")

 

[Dan]

JMB,

This works great!! So does T. Solutions. Thank you both. Could I bother you
to explain the syntax though? I would like to understand the
formula...specifically the A:A ???

Thanks - Dan

 

[JMB]

A:A refers to all of column A. It is the same as A1:A65536 (pre XL2007)

If B3 is 7 then
=IF(B3>3,MAX(INDEX(A:A, 4):INDEX(A:A, 7)),"")

Index returns the nth cell from column A
=IF(B3>3,MAX(A4:A7),"")

although I will point out that without the initial test of B3>3 and B3
happens to be 3, then the first index function becomes
Index(A:A, 0)
which will return the entire column A - which is sometimes a useful feature
of Index. This would not be a problem with Biff's formula (you'd get an
error as the range reference fed to the Indirect function would be A0:A3 and
is invalid).

Although if you insert a new column and shift column A to the right (so now
it is column B), the Indirect function in Biff's post will still reference
column A (XL would not change the formula automatically to reference column
B).

 

[Dan]

How about the ,"" on the end?

 

[JMB]

if b3 is <= 3 then IF will return an empty string - making the cell "blank"