Generieke formule
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Samenvatting
Om het gemiddelde van de laatste 5 gegevenspunten te berekenen, kunt u de GEMIDDELDE-functie samen met de COUNT- en VERSCHUIVINGS-functies gebruiken. U kunt deze benadering gebruiken om het gemiddelde van de laatste N gegevenspunten te nemen: laatste 3 dagen, laatste 6 metingen, etc. In het getoonde voorbeeld is de formule in F6:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Let op: een negatieve waarde voor hoogte werkt niet in Google-bladen. Zie hieronder voor meer informatie.
Uitleg
De OFFSET-functie kan worden gebruikt om dynamische rechthoekige bereiken te construeren op basis van een startreferentie en gegeven rijen, kolommen, hoogte en breedte. De argumenten voor rijen en kolommen functioneren als "offsets" ten opzichte van de beginverwijzing. De hoogte- en breedte-argumenten (beide optioneel) bepalen hoeveel rijen en kolommen het uiteindelijke bereik bevat. Voor dit voorbeeld is OFFSET als volgt geconfigureerd:
- referentie = C3
- rijen = AANTAL (A: A)
- cols = 0
- hoogte = -5
- width = (niet opgegeven)
De startreferentie wordt gegeven als C3, de cel boven de feitelijke gegevens. Omdat we willen dat OFFSET een bereik retourneert dat afkomstig is van de laatste invoer in kolom C, gebruiken we de functie COUNT om alle waarden in kolom C te tellen om de vereiste rijoffset te krijgen. COUNT telt alleen numerieke waarden, dus de kop in rij 3 wordt automatisch genegeerd.
Met 8 numerieke waarden in kolom C, wordt de OFFSET-formule omgezet in:
OFFSET(C3,8,0,-5)
Met deze waarden begint OFFSET bij C3, verschuift 8 rijen naar C11 en gebruikt dan -5 om het rechthoekige bereik "achterwaarts" 5 rijen uit te breiden om het bereik C7: C11 te creëren.
Ten slotte retourneert OFFSET het bereik C7: C11 naar de GEMIDDELDE functie, die het gemiddelde van waarden in dat bereik berekent.
Excel versus Spreadsheets
Een vreemde gril met deze formule is dat deze niet werkt met Google Spreadsheets, omdat de OFFSET-functie in Spreadsheets geen negatieve waarde voor hoogte- of breedte-argumenten toestaat. In Excel-documentatie staat ook dat hoogte of breedte niet negatief kunnen zijn, maar het lijkt erop dat negatieve waarden sinds de jaren negentig prima hebben gewerkt in Excel.
Om negatieve hoogte- of breedtewaarden te vermijden, kunt u een formule als deze gebruiken:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Let op C4 is in dit geval de startreferentie. De algemene vorm is:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
waarbij A1 de eerste cel is in de getallen die u wilt gemiddeld.
