Forum Discussion
Excel help needed please
I have a column of numbers made up of positive numbers and zeros. Starting from the bottom of the column, I need to find the last 8 non-zero numbers, subtract the two highest and the two lowest numbers, and average the remaining four numbers. Any help is appreciated. Thanks.
Using a few Beta functions:
=LET(f,FILTER(list,list<>0),s,SORT(DROP(f,ROWS(f)-8),1,-1),AVERAGE(CHOOSEROWS(s,{3,4,5,6})))- Patrick2788....just so I understand, should it be:
=LET(f,FILTER(A1:A100,A1:A100<>0),s,SORT(DROP(f,ROWS(f)-8),1,-1),AVERAGE(CHOOSEROWS(s,{3,4,5,6})))
I'm not sure what to put in for 'list'.
Thanks.Try this:
=LET(a,A1:A100,f,FILTER(a,a<>0),s,SORT(DROP(f,ROWS(f)-8),1,-1),AVERAGE(CHOOSEROWS(s,{3,4,5,6})))
Just out of curiosity, what does ``subtract the two highest and the two lowest numbers`` mean?!
If the numbers are H1 and H2 (two highest) and L1 and L2 (two lowest), do you mean L1+L2-H1-H2?
(That is, subtract the sum of the two highest from the sum of the two lowest?)
And do you realize that if we "subtract highest from lowest", the result is always negative (or zero only if all the numbers are the same)?
I'd be surprised that that it is your intent, since everything else is positive.
- Thanks for the reply. Here's an example to better illustrate. Let's say I have the following in column A:
4
2
5
0
2
9
7
9
0
7
6
0
Starting from the bottom and working upwards, the first eight non-zero numbers are: 6,7,9,7,9,2,5,2. From there, I need to ignore the highest two values and the lowest two values: 9,9 and 2,2. That leaves 6,7,7,5. Then I need to take the average of those four numbers, which gives the final answer of 6.25.
Hopefully that makes better sense, thanks for any help on this.For older version perhaps this works
=AVERAGE( LARGE( SMALL( INDEX( data, AGGREGATE(15, 6, 1/(data <> 0)*( ROW(data) - ROW(firstCell) +1 ), ROW(INDIRECT(COUNTIF(data, "<>0" )-7 & ":" & COUNTIF(data, "<>0" )) ) ) ), ROW(3:8) ), ROW(3:6) ) )
