Forum Discussion

TTTDVDDO's avatar
TTTDVDDO
Copper Contributor
Jul 30, 2022

CALCULATE THE AVERAGE OF A COLUMN BUT ONLY USING THE HIGHEST 8 VALUES OUT OF THE MOST RECENT 15

IN EACH COLUMN OF A WORKSHEET, EXCEPT THE FIRST 4 COLUMNS, CALCULATE THE AVERAGE OF 8 SELECTED CELLS.  BUT ONLY USE THE HIGHEST 8 CELL VALUES OUT OF THE MOST RECENT 15.  COLUMN 1 IS THE DATE COLUMN AND THE SHEET SHOULD BE SORTED BY THE DATE. (DATE FORMAT: 20220729=JULY 29, 2022). IF THERE ARE LESS THAN 15 VALUES IN A COLUMN, STILL USE THE HIGHEST 8 CELL VALUES IN THAT COLUMN. IF THERE ARE LESS THAN 8 CELL VALUES IN A COLUMN, AVERAGE THOSE.

excel
Formulas and Functions

2 Replies

Sort By
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jul 30, 2022

    TTTDVDDO 

    =LET(range;C2:C23;WENN(ANZAHL(range)<=8;
MITTELWERT(range);WENN(ANZAHL(range)<15;
LET(greatest;KGRÖSSTE(range;{1;2;3;4;5;6;7;8});
MITTELWERT(greatest));
LET(large;KGRÖSSTE(WENN(range<>"";ZEILE(2:23)-1);
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15});
numbers;INDEX(range;large);größte;
KGRÖSSTE(numbers;{1;2;3;4;5;6;7;8});
MITTELWERT(größte)))))

    You can try to translate and apply this formula which returns the expected results in my sheet. The range C2:C23 and row 2:23 can be adapted as required. I've entered the formula in cell C25 and copied to the right.

     

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jul 30, 2022

      OliverScheurich 

      Translation of the formula into English, courtesy of Excel-Translator :

       

      =LET(range,C2:C23,IF(COUNT(range)<=8,
AVERAGE(range),IF(COUNT(range)<15,
LET(greatest,LARGE(range,{1,2,3,4,5,6,7,8}),
AVERAGE(greatest)),
LET(large,LARGE(IF(range<>"",ROW(2:23)-1),
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}),
numbers,INDEX(range,large),größte,
LARGE(numbers,{1,2,3,4,5,6,7,8}),
AVERAGE(größte)))))

       

Resources