Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Max from Variable Range
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Max from Variable Range
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))) -- Biff Microsoft Excel MVP "Dan" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Max from Variable Range
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Max from Variable Range
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Max from Variable Range
How about the ,"" on the end?
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Max from Variable Range
if b3 is <= 3 then IF will return an empty string - making the cell "blank"
"Dan" wrote: How about the ,"" on the end? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cells.Find with a variable | Excel Discussion (Misc queries) | |||
Right Text - Variable Find | Excel Discussion (Misc queries) | |||
find a variable text string | Excel Discussion (Misc queries) | |||
3 variable find and replace | Excel Discussion (Misc queries) | |||
How do I find the last row of data and then use that as a variable | New Users to Excel |