Generisk formel
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Sammanfattning
För att genomsnitta de senaste 5 datapunkterna kan du använda AVERAGE-funktionen tillsammans med COUNT- och OFFSET-funktionerna. Du kan använda detta tillvägagångssätt för att genomsnittliga de senaste N-datapunkterna: de senaste 3 dagarna, de senaste 6 mätningarna etc. I exemplet som visas är formeln i F6:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Obs! Ett negativt värde för höjd fungerar inte i Google-ark. Se nedan för mer information.
Förklaring
OFFSET-funktionen kan användas för att konstruera dynamiska rektangulära intervall baserat på en startreferens och givna rader, kolumner, höjd och bredd. Argumenten för rader och kolumner fungerar som "förskjutningar" från startreferensen. Argumenten för höjd och bredd (båda valfria) bestämmer hur många rader och kolumner det slutliga intervallet innehåller. För detta exempel är OFFSET konfigurerad så här:
- referens = C3
- rader = COUNT (A: A)
- kol = 0
- höjd = -5
- bredd = (ingår ej)
Startreferensen tillhandahålls som C3 cellen ovanför de faktiska data. Eftersom vi vill att OFFSET ska returnera ett intervall som kommer från den sista posten i kolumn C, använder vi COUNT-funktionen för att räkna alla värden i kolumn C för att få önskad radförskjutning. COUNT räknar bara numeriska värden, så rubriken i rad 3 ignoreras automatiskt.
Med 8 numeriska värden i kolumn C löser OFFSET-formeln att:
OFFSET(C3,8,0,-5)
Med dessa värden börjar OFFSET vid C3, förskjuter 8 rader till C11 och använder sedan -5 för att utöka det rektangulära området uppåt "bakåt" 5 rader för att skapa intervallet C7: C11.
Slutligen returnerar OFFSET intervallet C7: C11 till AVERAGE-funktionen, som beräknar medelvärdet av värden i det intervallet.
Excel kontra ark
En udda egendom med denna formel är att den inte fungerar med Google Sheets, eftersom OFFSET-funktionen i Sheets inte tillåter ett negativt värde för höjd- eller breddargument. Excel-dokumentation anger också att höjd eller bredd inte kan vara negativ men det verkar som om negativa värden har fungerat bra i Excel sedan 1990-talet.
För att undvika negativa höjd- eller breddvärden kan du använda en sådan formel:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Meddelande C4 är startreferensen i detta fall. Den allmänna formen är:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
där A1 är den första cellen i de siffror du vill genomsnitta.
