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 numbe...
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.
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) ) )When I open your Excel example in Excel 2010 and comment out the new-version formula in C3 (which would create a #NAME error), I get a #NUM error for the old-version formula in C5.
At one point in the formula evaluation, the formula is reduced to (the cell references are actually "absolute"):
=AVERAGE(LARGE(SMALL(A2, ROW(3:8)), ROW(3:6)))
because the INDEX expression is reduced to INDEX(A1:A12, 2), which returns a single cell reference instead of a range.
The SMALL expression returns an array of all #NUM, presumably because the "range" A2 does not contain at least 8 rows.
Any idea how to fix this?
The formula in C5 is
{ =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) ) ) }with "data" defined as =Sheet1!$A$1:$A$12 and "firstCell" defined as =Sheet1!$A$1.
It depends on which Excel version / platform you are. As variant with latest functionality that could be
=LET( eight, TAKE( FILTER(data, data), -8), n, COUNTA( eight ), AVERAGE( SMALL( LARGE( eight, SEQUENCE( n- 2,,3 ) ), SEQUENCE( n - 4,,3 ) ) ) )- Thanks for the reply. Would I type the following into a cell? Assuming data is in A1:A100.
=LET(eight,TAKE(FILTER(A1,A100),-8),n,COUNTA(eight),AVERAGE(SMALL(LARGE(eight,SEQUENCE(n-2,,3)),SEQUENCE(n-4,,3))))That would be the formula if you are a Microsoft 365 Insider.
Oherwise, use
=AVERAGE(LARGE(SMALL(INDEX(A1:A100,AGGREGATE(15,6,1/(A1:A100<>0)*(ROW(A1:A100)-ROW(A1)+1),ROW(INDIRECT(COUNTIF(A1:A100,"<>0")-7&":"&COUNTIF(A1:A100,"<>0"))))),ROW(3:8)),ROW(3:6)))
