Excel help needed please

Copper Contributor

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.

17 Replies

@tstecklein 

 

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.

@tstecklein 

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 ) ) ) )

@tstecklein 

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) ) )
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))))

@tstecklein 

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)))

@Sergei Baklan 

 

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.

@Hans Vogelaar 

 

Your formula also returns #NUM when I array-enter it into C7 of Sergei's Excel file.

 

I did not bother to compare your formula with Sergei's.

 

And I did not bother to step through the formula evaluation of your formula as carefully as I did with Sergei's.  I would need to scale down the range, since I cannot "see the forest for the trees" with a range of 100 rows.

 

But it appears that for your formula, the #NUM occurs in the second parameter of the INDEX expression, before the SMALL expression evaluation.  That is, the INDEX expression is reduce to INDEX(A1:A100, #NUM).

@Hans Vogelaarand @Sergei Baklan 

 

I would really like to get your old-version formula(s) to work in Excel 2010, since they appear to go in the direction that I was trying, but I could not develop the formula on my own.

@Joe User 

I only replaced data with A1:A100, the range the OP named.

It works for me in Excel 2021; I don't have an older version to test with.

@Hans Vogelaar  wrote:  ``I only replaced data with A1:A100``

 

Odd, then, that the #NUM occurs in a different way.

 

@Hans Vogelaar  wrote:  ``It works for me in Excel 2021``

 

Grumble!  I suspect that the problem might be the behavior of INDEX in that context.  Wild guess, really.  But I have developed formulas where INDEX as a subfunction of SUMPRODUCT should return a range, but it does not.  One user in another forum is adept at creating arcane formulas to work around the problem.  And perhaps it has been fixed in Excel 2021 (or earlier).

 

Oh well, maybe someday it will pique my interest enough to debug the issue with Excel 2010.   My old-version approach would be to resort to using VBA.

Thanks much!

@tstecklein 

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})))

@Joe User 

Perhaps to change SMALL and LARGE with another AGGREGATE, but not sure. Unfortunately don't have pre-DA Excel to play with formula. Sorry for wrong suggestion.

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.

@tstecklein 

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})))
Thank you.