Dynamic averaging based on adjacent column.

**projectatpel** · 11-01-2013, 11:37 AM

I have previously posted a similar query. This looks same as well, but this is different
I have a column with random values that are generated my my machinery.
I should not sort them, because they provide me with the performance graph.
Lets say i have column "A"
0
2
9
9.5
30
90
22
15
9
0

As the max value of this is 90, and its 10% of MAX is 9, which occurs at 3rd and 9 th positions.
I need to find the average of its adjacent range, with zeros not considered.
It has to calculate the max value in the column, find 10% of max and do average of adjacent column from the sl. no of first occurrence of 10% value in first column to the sl no of last occurrence of 10% value in first column.

the exact 10% value may not always exist. 10% of 90 is 9, but if the max value is 94, the 10% would be 9.4 which doesn't exist. then it has to take the nearest available predecessor in that case.
i get values in decimals mostly and only some of them are perfect integers, thus this consideration goes with it,and also, my range actually is B14:B10013
i would most probably have the 10% value within the first 50 "non zero" entries and reoccurring somewhere probably within the last 50 "non zero" entries.

attached sample file stating what i need, illustrated.

I need this urgently for my project work!