Excel formula: Prosječne posljednje 3 numeričke vrijednosti -

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.

Zanimljivi članci...