Generička formula
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Sažetak
Da biste prosječno izračunali posljednje 3 numeričke vrijednosti u rasponu, možete upotrijebiti formulu niza temeljenu na kombinaciji funkcija za unos posljednjih n numeričkih vrijednosti u AVERAGE funkciju. U prikazanom primjeru formula u D6 je:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
gdje je "podatak" imenovani raspon B5: B13.
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter.
Obrazloženje
Funkcija AVERAGE izračunavat će prosjek brojeva predstavljenih u nizu, pa je gotovo sav posao u ovoj formuli generiranje niza od posljednje 3 numeričke vrijednosti u rasponu. Funkcionirajući iznutra prema van, funkcija IF koristi se za "filtriranje" numeričkih vrijednosti:
IF(ISNUMBER(data),ROW(data))
Funkcija ISNUMBER vraća TRUE za numeričke vrijednosti, a FALSE za ostale vrijednosti (uključujući praznine), a funkcija ROW vraća brojeve redaka, pa je rezultat ove operacije niz redaka koji odgovaraju numeričkim unosima:
(5;6;FALSE;8;9;10;FALSE;12;13)
Ovaj niz ulazi u funkciju LARGE s konstantom niza (1,2,3) za k. LARGE automatski zanemaruje FALSE vrijednosti i vraća niz s najveća 3 broja, koji odgovaraju zadnja 3 retka s numeričkim vrijednostima:
(13,12,10)
Ovaj niz ulazi u funkciju LOOKUP kao vrijednost pretraživanja. Pregledni niz pruža funkcija ROW, a rezultatni niz je imenovani raspon "podaci":
LOOKUP((13,12,10), ROW(data), data))
LOOKUP zatim vraća niz koji sadrži odgovarajuće vrijednosti u "podacima", a koji se unose u AVERAGE:
=AVERAGE((100,92,90))
Rukovanje s manje vrijednosti
Ako broj numeričkih vrijednosti padne ispod 3, ova će formula vratiti pogrešku #NUM jer LARGE neće moći vratiti 3 vrijednosti kako je zatraženo. Jedan od načina da se to riješi je zamjena čvrsto kodirane konstante niza (1,2,3) dinamičkim nizom stvorenim pomoću INDIRECT-a poput ovog:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Ovdje se MIN koristi za postavljanje gornje granice niza na 3 ili stvarnog broja numeričkih vrijednosti, ovisno o tome što je manje.
Napomena: Na ovaj pametni pristup naišao sam na chandoo.org, u odgovoru Sajana na slično pitanje.
