Forum Discussion

tstecklein's avatar
tstecklein
Copper Contributor
Jun 12, 2022

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...
excel
tstecklein's avatar
tstecklein
Copper Contributor
Jun 12, 2022
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.
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 12, 2022

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) ) )
  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor
    Jun 12, 2022

    SergeiBaklan 

     

    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.

Resources