Generisk formel
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Resumé
For at gennemsnitliggøre de sidste 3 numeriske værdier i et interval kan du bruge en matrixformel baseret på en kombination af funktioner til at føje de sidste n numeriske værdier til AVERAGE-funktionen. I det viste eksempel er formlen i D6:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
hvor "data" er det navngivne område B5: B13.
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter.
Forklaring
AVERAGE-funktionen beregner et gennemsnit af tal, der præsenteres i en matrix, så næsten alt arbejdet i denne formel er at generere en matrix med de sidste 3 numeriske værdier i et interval. Arbejder indefra og ud, IF-funktionen bruges til at "filtrere" numeriske værdier:
IF(ISNUMBER(data),ROW(data))
ISNUMBER-funktionen returnerer SAND for numeriske værdier og FALSK for andre værdier (inklusive tomme felter), og ROW-funktionen returnerer rækkenumre, så resultatet af denne operation er et array-række tal, der svarer til numeriske poster:
(5;6;FALSE;8;9;10;FALSE;12;13)
Denne matrix går ind i LARGE-funktionen med arraykonstanten (1,2,3) for k. LARGE ignorerer automatisk FALSE-værdierne og returnerer en matrix med de største 3 tal, der svarer til de sidste 3 rækker med numeriske værdier:
(13,12,10)
Denne matrix går ind i LOOKUP-funktionen som opslagsværdi. Opslagsarrayet leveres af ROW-funktionen, og resultatarrayet er det navngivne interval "data":
LOOKUP((13,12,10), ROW(data), data))
LOOKUP returnerer derefter en matrix, der indeholder tilsvarende værdier i "data", som føres ind i GEMIDDEL:
=AVERAGE((100,92,90))
Håndtering af færre værdier
Hvis antallet af numeriske værdier falder til under 3, returnerer denne formel fejlen #NUM, da LARGE ikke kan returnere 3 værdier som ønsket. En måde at håndtere dette på er at erstatte den hårdkodede arraykonstant (1,2,3) med et dynamisk array oprettet ved hjælp af INDIRECT som denne:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Her bruges MIN til at indstille den øvre grænse for arrayet til 3 eller det faktiske antal numeriske værdier, alt efter hvad der er mindre.
Bemærk: Jeg løb ind i denne kloge tilgang over på chandoo.org i et svar fra Sajan på et lignende spørgsmål.
