Generisk formel
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Sammanfattning
För att medelvärde de senaste 3 numeriska värdena i ett intervall kan du använda en matrisformel baserad på en kombination av funktioner för att mata de sista n numeriska värdena till AVERAGE-funktionen. I exemplet som visas är formeln i D6:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
där "data" är det namngivna området B5: B13.
Obs: detta är en matrisformel och måste anges med kontroll + skift + enter.
Förklaring
AVERAGE-funktionen beräknar ett genomsnitt av siffror som presenteras i en matris, så nästan allt arbete i denna formel är att generera en matris med de tre sista numeriska värdena i ett intervall. Arbetar inifrån och ut och IF-funktionen används för att "filtrera" numeriska värden:
IF(ISNUMBER(data),ROW(data))
ISNUMBER-funktionen returnerar SANT för numeriska värden och FALSE för andra värden (inklusive tomma), och ROW-funktionen returnerar radnummer, så resultatet av denna operation är ett arrayradnummer som motsvarar numeriska poster:
(5;6;FALSE;8;9;10;FALSE;12;13)
Denna matris går in i LARGE-funktionen med arraykonstanten (1,2,3) för k. LARGE ignorerar automatiskt FALSE-värdena och returnerar en matris med de största tre siffrorna, vilket motsvarar de sista 3 raderna med numeriska värden:
(13,12,10)
Denna matris går in i LOOKUP-funktionen som uppslagsvärde. Uppslagsmatrisen tillhandahålls av ROW-funktionen, och resultatmatrisen är namnet på intervallet "data":
LOOKUP((13,12,10), ROW(data), data))
LOOKUP returnerar sedan en matris som innehåller motsvarande värden i "data", som matas in i GENOMSNITT:
=AVERAGE((100,92,90))
Hantering av färre värden
Om antalet numeriska värden sjunker under 3 kommer den här formeln att returnera #NUM-felet eftersom LARGE inte kan returnera 3 värden enligt begäran. Ett sätt att hantera detta är att ersätta den hårdkodade arraykonstanten (1,2,3) med en dynamisk array skapad med INDIRECT så här:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Här används MIN för att ställa in den övre gränsen för matrisen till 3 eller det faktiska antalet numeriska värden, beroende på vilket som är mindre.
Obs: Jag stötte på detta smarta tillvägagångssätt på chandoo.org, i ett svar från Sajan på en liknande fråga.
