Finding MAX in an array of formulas

[jackleesteere]

Hi there.
Can someone please help me with the following problem. In the attached spreadsheet I have a list of random numbers from rows 1-20. I want to create a formula that finds the maximum number in the list between rows 6 and 18. I have started by finding and displaying the cell address of the boundaries (A6 and A18) using the =ADDRESS(fn), however when i use this array in the =MAX(fn) it gives me 0. Instead i want the formula to yield 97, which is the maximum number within the array of A6:A18. I understand why it's doing this as the function is looking at C1:C2 instead of A6:A18, but I am wondering if there is another function that will yield my answer through a formula.
Of course i could just select the array (i.e. MAX(A6:A18), however I need to generate the answer using formulas as this example is a simplified version of a much larger and sophisticated spreadsheet I am creating.
Would really appreciate your help.
Thanks

[Kaper]

Please Login or Register  to view this content.

shall do

[hemesh]

try this
=MAX(INDIRECT(C1&":"&C2))

[jackleesteere]

Great. Thanks Kaper & Hemesh.

[jackleesteere]

Kaper & Hemesh. One more question...
I have updated the sheet attached (Book Rev1) to minimise the number of functions required on Sheet1. Im wondering what i have to do to the formula in C1 on Sheet2 to produce the same answer (97) as in C1 on Sheet1.
Thanks

[hemesh]

try this on sheet2 c1
=MAX(INDIRECT("Sheet1!"&ADDRESS(6,1,4)&":"&ADDRESS(18,1,4)))

if your sheet name has space like "sheet 1" then use
=MAX(INDIRECT("'"&"Sheet 1"&"'!"&ADDRESS(6,1,4)&":"&ADDRESS(18,1,4)))

[jackleesteere]

Much appreciated hemesh. That answered by question, however my final formula is rather large. i am wondering if there is a simplified function that can directly calculate the highlighted cells (C2 & D2) on Sheet2 of the spreadsheet (Book1.Rev2) attached. Many thanks.

[tigeravatar]

Hello jackleesteere,

Using your most recent example file, use this formula in cell C2 of Sheet2:

Please Login or Register  to view this content.

And this formula in cell D2 of Sheet2:

Please Login or Register  to view this content.