Generieke formule
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Samenvatting
Om het gemiddelde van de laatste 5 gegevenswaarden in een reeks kolommen te berekenen, kunt u de GEMIDDELDE-functie samen met de COUNT- en VERSCHUIVINGS-functies gebruiken. In het getoonde voorbeeld is de formule in F6:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Uitleg
De OFFSET-functie kan worden gebruikt om dynamische bereiken te construeren met behulp van een startcel 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. We willen dat OFFSET een bereik retourneert dat begint bij de laatste invoer en "achteruit" wordt uitgebreid, dus we leveren argumenten als volgt:
verwijzing - de beginverwijzing is D5 - de cel direct rechts van de formule en de eerste cel in het waardenbereik waarmee we werken.
rijen - we gebruiken 0 voor het argument rijen, omdat we in dezelfde rij willen blijven.
kolommen - voor het kolommen-argument gebruiken we de COUNT-functie om alle waarden in het bereik te tellen en vervolgens 5 af te trekken. Hierdoor wordt het begin van het bereik 5 kolommen naar links verschoven.
hoogte - we gebruiken 1 omdat we een bereik van 1 rij willen als het eindresultaat.
breedte - we gebruiken 5, omdat we een eindbereik met 5 kolommen willen.
Voor de formule in C5 retourneert OFFSET het laatste bereik van F5: J5. Dit gaat naar de GEMIDDELDE functie die het gemiddelde van de 5 waarden in het bereik retourneert
Minder dan 5 waarden
Als er minder dan 5 waarden zijn, retourneert de formule een kringverwijzingsfout, aangezien het bereik teruggaat tot in de cel die de formule bevat. Om deze fout te voorkomen, kunt u de formule als volgt aanpassen:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Hier gebruiken we de MIN-functie om situaties te "vangen" waarin er minder dan 5 waarden zijn, en gebruiken we de werkelijke telling als die er zijn.
