Generisk formel
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Sammendrag
For å gjennomsnittliggjøre de siste 3 numeriske verdiene i et område, kan du bruke en matriseformel basert på en kombinasjon av funksjoner for å mate de siste n numeriske verdiene i GJENNOMSNITT-funksjonen. I eksemplet vist er formelen i D6:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
der "data" er det navngitte området B5: B13.
Merk: dette er en matriseformel, og må angis med kontroll + skift + enter.
Forklaring
AVERAGE-funksjonen vil beregne et gjennomsnitt av tall som presenteres i en matrise, så nesten alt arbeidet i denne formelen er å generere en matrise med de tre siste numeriske verdiene i et område. IF-funksjonen fungerer fra innsiden og ut og brukes til å "filtrere" numeriske verdier:
IF(ISNUMBER(data),ROW(data))
ISNUMBER-funksjonen returnerer SANT for numeriske verdier, og FALSE for andre verdier (inkludert blanke), og ROW-funksjonen returnerer radnumre, så resultatet av denne operasjonen er et array-radnummer som tilsvarer numeriske oppføringer:
(5;6;FALSE;8;9;10;FALSE;12;13)
Denne matrisen går inn i STOR funksjon med matrisekonstanten (1,2,3) for k. LARGE ignorerer automatisk FALSE-verdiene og returnerer en matrise med de største 3 tallene, som tilsvarer de siste 3 radene med numeriske verdier:
(13,12,10)
Denne matrisen går inn i LOOKUP-funksjonen som oppslagsverdien. Oppslagsmatrisen er levert av ROW-funksjonen, og resultatmatrisen er det navngitte området "data":
LOOKUP((13,12,10), ROW(data), data))
LOOKUP returnerer deretter en matrise som inneholder tilsvarende verdier i "data", som mates inn i GJENNOMSNITT:
=AVERAGE((100,92,90))
Håndtering av færre verdier
Hvis antallet numeriske verdier synker under 3, returnerer denne formelen feilen #NUM siden LARGE ikke vil være i stand til å returnere 3 verdier etter forespørsel. En måte å håndtere dette på er å erstatte den hardkodede matrisekonstanten (1,2,3) med en dynamisk matrise opprettet ved hjelp av INDIRECT slik:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Her brukes MIN til å sette den øvre grensen til matrisen til 3 eller det faktiske antallet numeriske verdier, avhengig av hvilken som er mindre.
Merk: Jeg løp inn i denne smarte tilnærmingen på chandoo.org, i et svar fra Sajan på et lignende spørsmål.
