Max Function with range refering to other cell... Unsuccessful

[bigjohn]

Hi,


Working with Max Function as described above.


This is the formula that got stuck with:


=MAX("G"&(D1-C1):"G"&(D1))


D1 = LARGE(E1:E100,1)

C1 = Number of row (Data, Not a formula)


Tried the array key press but result, #value.

 

[NARAYANK991]

Hi ,


Try this , entered as a normal formula , NOT an array formula :


=MAX(INDIRECT("G"&(D1-C1)):INDIRECT("G"&D1))


I entered some values in column E , so that D1 returned the value 99 as the maximum value in the range E1:E100.


Then I entered the value 73 in cell C1.


The above formula then returned the maximum value in the range G26:G99 , since D1-C1 = 99-73 = 26.


Narayan

 

[bigjohn]

Narayank,


This is what I've been looking for. Thank you.