Generieke formule
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Samenvatting
Om het gemiddelde van de laatste 3 numerieke waarden in een bereik te berekenen, kunt u een matrixformule gebruiken op basis van een combinatie van functies om de laatste n numerieke waarden in de GEMIDDELDE-functie in te voeren. In het getoonde voorbeeld is de formule in D6:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
waarbij "data" het benoemde bereik B5: B13 is.
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter.
Uitleg
De GEMIDDELDE functie berekent een gemiddelde van getallen gepresenteerd in een array, dus bijna al het werk in deze formule is het genereren van een array van de laatste 3 numerieke waarden in een bereik. De IF-functie werkt van binnen naar buiten en wordt gebruikt om numerieke waarden te "filteren":
IF(ISNUMBER(data),ROW(data))
De functie ISNUMBER retourneert WAAR voor numerieke waarden en ONWAAR voor andere waarden (inclusief spaties), en de functie RIJ retourneert rijnummers, dus het resultaat van deze bewerking is een matrix met rijnummers die overeenkomen met numerieke invoer:
(5;6;FALSE;8;9;10;FALSE;12;13)
Deze array gaat naar de LARGE-functie met de arrayconstante (1,2,3) voor k. LARGE negeert automatisch de FALSE-waarden en retourneert een array met de grootste 3 getallen, die overeenkomen met de laatste 3 rijen met numerieke waarden:
(13,12,10)
Deze array gaat naar de functie ZOEKEN als de opzoekwaarde. De opzoekmatrix wordt geleverd door de ROW-functie en de resultaatmatrix is het benoemde bereik "data":
LOOKUP((13,12,10), ROW(data), data))
LOOKUP retourneert vervolgens een array met overeenkomstige waarden in "data", die wordt ingevoerd in AVERAGE:
=AVERAGE((100,92,90))
Omgaan met minder waarden
Als het aantal numerieke waarden onder de 3 zakt, retourneert deze formule de fout #GETAL, aangezien LARGE geen 3 waarden kan retourneren zoals gevraagd. Een manier om hiermee om te gaan, is door de hardgecodeerde arrayconstante (1,2,3) te vervangen door een dynamische array die als volgt met INDIRECT is gemaakt:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Hier wordt MIN gebruikt om de bovengrens van de array in te stellen op 3 of het werkelijke aantal numerieke waarden, welke van beide het kleinst is.
Opmerking: ik kwam deze slimme aanpak tegen op chandoo.org, in een antwoord van Sajan op een soortgelijke vraag.
