Forum Discussion
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 A...
=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.
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)))))
