Take AVERAGE of a Particular Part of an Array

[-bobcat]

I've dug through several other threads but can't seem to find another quite like the problem I'm facing.
I'm trying to take an average of a section of an array of data that starts at a particular value, decreases to a minimum then increases to the maximum value, then settles back to that particular value for the rest of the array.

Basically the formula will need to look at the set of data beginning at the maximum value and find the average of the data from the first occurance of a particular value (50, for example) after the maximum and then return the average of all data from that first occurance of "50" to the end of the row. The rows all end on a set columm, say column Z.

I realize that may not be the best explanation, so I've included a picture to explan what each row of the data looks like.

example.png

Now if this data all had an exact column where this first occurance happens, it would be a simple fix, however this first occurance happens on different columns for each row of data as well as the maximum value for each row. Manual calculation is out of the question as I have over 5,000 rows on a excel sheet.

If anyone can tell me if this is possibe, I'd appreciate it. Also if further explanation is needed, please let me know.


Thanks!!

[Pepe Le Mokko]

Hi
Nice picture but a small sample sheet would be most helpful

[-bobcat]

Pepe Le Mokko,

Below is an example of the data in question...

excel help.xlsx

Thanks.

Hi
Nice picture but a small sample sheet would be most helpful

[Pepe Le Mokko]

I don't understand your requirements.
In row 1 f i the max value is 50,5 in col S and the first time 50 appears is in col AS - Thereafter the only values stay at 50

I must be missing something

[-bobcat]

Correct. However, there could be an instance in which for one cell the value increases to 50.01 and then returns to 50.0 or may dip below to 49.99 and then return to 50.0. So, for each row, the average of this section may not always be 50.

Here is a better example of the data. I apologize for the confusion.

excel help.xlsx

[jason.b75]

Where BC1 holds the value to look for after the max value,

Please Login or Register  to view this content.

[-bobcat]

Jason.b75,

Thank you so much! The formula works perfectly!!!!


You're awesome.