Generička formula
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Sažetak
Da biste prosječno odredili zadnjih 5 podataka u rasponu stupaca, možete koristiti funkciju PROSJEČIVANJE zajedno s funkcijama COUNT i OFFSET. U prikazanom primjeru formula u F6 je:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Obrazloženje
Funkcija OFFSET može se koristiti za izradu dinamičkih raspona pomoću početne ćelije i zadanih redaka, stupaca, visine i širine.
Argumenti redaka i stupaca funkcioniraju poput "odstupanja" od početne reference. Argumenti visine i širine, oba neobavezna, određuju koliko redaka i stupaca uključuje konačni raspon. Želimo da OFFSET vrati raspon koji započinje kod zadnjeg unosa i širi se "unatrag", tako da dostavljamo argumente kako slijedi:
referenca - početna referenca je D5 - ćelija izravno s desne strane formule i prva ćelija u rasponu vrijednosti s kojima radimo.
redovi - koristimo 0 za argument redovi, jer želimo ostati u istom retku.
stupci - za argument stupci koristimo funkciju COUNT za brojanje svih vrijednosti u rasponu, a zatim oduzimamo 5. To pomiče početak stupca 5 u lijevo.
visina - koristimo 1 jer želimo konačni rezultat raspon od 1 reda.
širina - koristimo 5, jer želimo konačni raspon s 5 stupaca.
Za formulu u C5, OFFSET vraća konačni raspon F5: J5. To ulazi u funkciju AVERAGE koja vraća prosjek od 5 vrijednosti u rasponu
Manje od 5 vrijednosti
Ako postoji manje od 5 vrijednosti, formula će vratiti kružnu referentnu pogrešku, jer će se raspon proširiti natrag u ćeliju koja sadrži formulu. Da biste spriječili ovu pogrešku, formulu možete prilagoditi na sljedeći način:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Ovdje koristimo funkciju MIN za "hvatanje" situacija u kojima postoji manje od 5 vrijednosti i koristimo stvarni broj kada postoje.
