Find MAX, look down 4 rows from the MAX, return that value

[Jdawg]

I am working with Hydrographs (streamflow) and need to
separate out the low flow vs. the high flows. I can
easily find the maximum (a rain event), once that is
found I need to move forward in time 4 days (down 4
rows), find that number and average all values below it.
I can do most of it with an IF statement, however I don't
know how to tell excel to "look down 4 rows from the
MAX".

Any help would be great!

 

[Don Guillett]

Have a look in HELP for OFFSET to combine with your MAX function

 

[Frank Kabel]

Hi
try something like the following:
=AVERAGE(OFFSET($A$1,MATCH(MAX(A:A),A:A,0)+3,0,1000))

 

[Aladin Akyurek]

Let column A house the data of interest.

In C2 enter:

=MATCH(MAX(A:A),A:A,0)+4

In C3 enter:

=MATCH(9.99999999999999E+307,A:A)

In C4 enter:

=IF(C3>=C2,AVERAGE(INDEX(A:A,C2):INDEX(A:A,C3)),"Insufficient Data")

Caveat. The formula in C2 will calculate the position of the first instance
of the max value and the average will be based on that position.