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
|