Variable length averages

[HikerBrian]

Hi,

I have 20 lists of burner temperatures from an oven test I'd like to analyze, and I need to find the average after it finishes the initial heating cycle. All the data lists are different lengths, and I'm wondering if there is a way I can tell Excel to average the data from the MAX value (wherever that is) to the end of the column.

Here is a brief example:

125
361
536
746
964
1151
1203
1181
965
754
836
792
821
787

I'm interested in averaging the part of the column after the max to the end (in red), but I don't know how my cells are before the max! Obviously I could just scroll down and find the max, but with the amount of data I have (and I'll be taking more every day) I'd like to automate it.

Thanks!!

 

[barry houdini]

Assuming you have data down to row 100 at the most (adjust as required) try this formula

=AVERAGE(INDEX(A2:A100,MATCH(MAX(A2:A100),A2:A100,0)+1):A100)

 

[HikerBrian]

Incredible. Thanks!