Generisk formel
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Sammanfattning
För att medelvärde de senaste 5 datavärdena i ett antal kolumner kan du använda AVERAGE-funktionen tillsammans med COUNT- och OFFSET-funktionerna. I exemplet som visas är formeln i F6:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Förklaring
OFFSET-funktionen kan användas för att konstruera dynamiska intervall med en startcell och givna rader, kolumner, höjd och bredd.
Argumenten för rader och kolumner fungerar som "förskjutningar" från startreferensen. Argumenten höjd och bredd, båda valfria, avgör hur många rader och kolumner det slutliga intervallet innehåller. Vi vill att OFFSET ska returnera ett intervall som börjar vid den senaste posten och expanderar "bakåt" så vi tillhandahåller argument enligt följande:
referens - startreferensen är D5 - cellen direkt till höger om formeln och den första cellen i det värdeområde vi arbetar med.
rader - vi använder 0 för argumentet rader, eftersom vi vill stanna i samma rad.
kolumner - för kolumnerargumentet använder vi COUNT-funktionen för att räkna alla värden i intervallet och sedan subtrahera 5. Detta förskjuter början av intervallet 5 kolumner till vänster.
höjd - vi använder 1 eftersom vi vill ha ett 1-radsintervall som slutresultat.
bredd - vi använder 5, eftersom vi vill ha ett slutligt intervall med 5 kolumner.
För formeln i C5 returnerar OFFSET ett slutligt intervall på F5: J5. Detta går in i AVERAGE-funktionen som returnerar medelvärdet av de 5 värdena i intervallet
Mindre än 5 värden
Om det finns färre än 5 värden returnerar formeln ett cirkulärt referensfel, eftersom intervallet sträcker sig tillbaka till cellen som innehåller formeln. För att förhindra detta fel kan du anpassa formeln enligt följande:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Här använder vi MIN-funktionen för att "fånga" situationer där det finns mindre än 5 värden och använder det faktiska antalet när det finns.
