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

  • Thread starter Thread starter Jdawg
  • Start date Start date
J

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!
 
Hi
try something like the following:
=AVERAGE(OFFSET($A$1,MATCH(MAX(A:A),A:A,0)+3,0,1000))
 
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.
 
Back
Top