Dynamic Averaging of column.

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
3
9
7
90
22
5
9
0

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

I need this urgently for my project work!

hope this works! Btw thanx for the help! Will check and post back!

Originally Posted by XOR LX

Edit: I just re-read your post and realise that you want zeroes within the range excluded from the average. Amend to:

=AVERAGEIF(INDEX(A2:A1000,MATCH(MAX(A2:A1000)*0.1,A2:A1000,0)):INDEX(A2:A1000,MATCH(2,INDEX(1/((A2:A1000=MAX(A2:A1000)*0.1)*ROW(A2:A1000)),,))),"<>0")

Regards

it is actually returning a #NA error!

Originally Posted by XOR LX

Edit: I just re-read your post and realise that you want zeroes within the range excluded from the average. Amend to:

=AVERAGEIF(INDEX(A2:A1000,MATCH(MAX(A2:A1000)*0.1,A2:A1000,0)):INDEX(A2:A1000,MATCH(2,INDEX(1/((A2:A1000=MAX(A2:A1000)*0.1)*ROW(A2:A1000)),,))),"<>0")

Regards

i think, that is because the exact 10% value doesn't exist or 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.

Originally Posted by XOR LX

Edit: I just re-read your post and realise that you want zeroes within the range excluded from the average. Amend to:

=AVERAGEIF(INDEX(A2:A1000,MATCH(MAX(A2:A1000)*0.1,A2:A1000,0)):INDEX(A2:A1000,MATCH(2,INDEX(1/((A2:A1000=MAX(A2:A1000)*0.1)*ROW(A2:A1000)),,))),"<>0")

Regards

i get values in decimals mostly and only some of them are perfect integers, thus this consideration goes with it,and also, my range is B14:B10013

"Can you just clarify what you mean by "nearest available predecessor?

So, if 9.4 didn't exist, but 9.5 and 8.6 did (and were the two absolutely closest in value), which would it be? Or did you have something else in mind by "predecessor"?

Regards"
hmm... oh! this is much more tricky than I expected....
Ohk, then it has to take 8.6, coz in my case, the values are generally not so far apart.
if there is no 9.4, there is a high chance of 9.3 or 9.2 or at least 9.1 to exist.
and even in the extreme case, when the 10% of max occurs the last, it has to take predecessor i.e. 8.6 preferred over 9.5

the values would actually be gradually increasing and then again decreasing, so i think such a problem will not occur. i would most probably have the 10% value within the first 30 "non zero" entries and reoccurring within the last 30 "non zero" entries.

Originally Posted by XOR LX

Hang on - are you sure you've thought this through?

I mean, what if you have a situation such as:

Max Value: 90

No entries of 9 in the data, but one entry of 8.97 and two of 8.98

8.97 is in row 1 (out of 13000)
8.98 is in rows 12500 and 12950

In this case, since you want to take the first occurrence of the nearest value less than or equal to 10% of the max, which is 8.98 in this case, you would end up taking the average over rows 12500:12950 only, though this seems a touch arbitrary since it is only 1/100th out of being rows 1:12950 (had 8.97 been the closest).

Do you see what I mean? What exactly are you trying to do here? What statistic are you trying to represent?

Regards

but trimmean uses the exact same amount of trimming every time.
But in my case, the percentage of trimming changes with the change in max value.
And also it is not possible that the first and last occurrences of 10% value is equidistant from both ends.

YES! That is how they occur. One occurrence before max and the other after max value.

Originally Posted by XOR LX

Can I assume that, to determine the latest occurrence of 10% of the mean (or nearest thereof), I can begin the search in a range from the max value onwards (since you said it is generally rising to this max, then falling again)?

Basically, this is equivalent to saying that the first and last occurrences of these two end-points cannot both occur in the first half of the range (leading up to the max).

Regards

Attached herein is a file. as the max value of changes, the position of the 10% value also changes.
the formula has to consider that position change.

Sample.xlsx

Originally Posted by XOR LX

Could you kindly upload a sample sheet with your desired results? It's getting a little difficult to do this purely theoretically, and it would be useful to have some data and an end result to aim for.

Regards

looks like this is already solved, but i wanted to see if i could do it any simpler (doesn't look like it). Anyway, I believe this does exactly what you asked:

Formula:

Please Login or Register  to view this content.

but this much simpler formula also seems to work with your data-set:

Formula:

Please Login or Register  to view this content.

this simpler formula breaks if your data gaps up a little on one side or the other... ie say 10% of the max were 9, on one side you had a 8 and a 10 and on the other side you had a 7 and a 13. this second formula would not include the 7 in the average even though that is the closest value on that side of the max... not sure how precise you needed to be.

both are array formulas btw.

Thanx a lot! this seems to be working very much fine!

Originally Posted by XOR LX

Based on this sheet, try this array (important) formula:

=AVERAGEIF(INDEX($B$2:$B$4759,MATCH(TRUE,ABS($B$2:INDEX($B$2:$B$4759,MATCH(MAX($B$2:$B$4759),$B$2:$B$4759,0))-MAX($B$2:$B$4759)*0.1)=MIN(ABS($B$2:INDEX($B$2:$B$4759,MATCH(MAX($B$2:$B$4759),$B$2:$B$4759,0))-MAX($B$2:$B$4759)*0.1)),0)):INDEX(INDEX($B$2:$B$4759,MATCH(MAX($B$2:$B$4759),$B$2:$B$4759,0)):$B$4759,MATCH(TRUE,ABS(INDEX($B$2:$B$4759,MATCH(MAX($B$2:$B$4759),$B$2:$B$4759,0)):$B$4759-MAX($B$2:$B$4759)*0.1)=MIN(ABS(INDEX($B$2:$B$4759,MATCH(MAX($B$2:$B$4759),$B$2:$B$4759,0)):$B$4759-MAX($B$2:$B$4759)*0.1)),0)),"<>0")

Regards