View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Find Max from Variable Range

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

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

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

although I will point out that without the initial test of B33 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" wrote:

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" wrote:

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

"Dan" wrote:

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