GEMIDDELDE.ALS en GEMIDDELDEN.ALS: voorbeelden met één of meer voorwaarden, en waarom je #DEEL/0! krijgt

Wat doen GEMIDDELDE.ALS en GEMIDDELDEN.ALS?

GEMIDDELDE.ALS neemt het gemiddelde van de getallen die aan één voorwaarde voldoen, GEMIDDELDEN.ALS van de getallen die aan al je voorwaarden voldoen. =GEMIDDELDE.ALS(B2:B16;"Kleding";C2:C16) geeft 7,67 euro, de gemiddelde prijs van een stuk kleding. In een Engelstalige Excel heten de functies AVERAGEIF en AVERAGEIFS.

Alle voorbeelden rekenen op een week verkopen van Kringwinkel Tweede Kans in Gent, de vierde klant in onze Excel-cursus. Wil je het stap voor stap leren, volg dan de les over een gemiddelde met voorwaarde. Zoek je het gewone gemiddelde, een gewogen gemiddelde of de werking van GEMIDDELDE zelf, lees dan hoe je een gemiddelde berekent. Dit artikel gaat over de varianten met een voorwaarde.

Hoe schrijf je GEMIDDELDE.ALS en GEMIDDELDEN.ALS?

De namen schelen één letter, maar de volgorde verschilt. GEMIDDELDE.ALS zet de getallen achteraan, en dat argument is optioneel (Microsoft). GEMIDDELDEN.ALS zet ze vooraan, gevolgd door tot 127 paren van een bereik en een criterium (Microsoft).

=GEMIDDELDE.ALS(bereik;criteria;[gemiddelde_bereik])
=GEMIDDELDEN.ALS(gemiddelde_bereik;criteriabereik1;criterium1;[criteriabereik2;criterium2];…)

In een Engelstalige Excel schrijf je =AVERAGEIF(B2:B16,"Kleding",C2:C16) en =AVERAGEIFS(C2:C16,B2:B16,"Kleding",E2:E16,"Sofie"). Neem je een formule over, vertaal dan de functienaam en de komma's.

Hoe bereken je een gemiddelde per categorie?

Coördinator Yasmina van Tweede Kans wil weten wat een stuk kleding gemiddeld kost, en of dat meer is dan huisraad. Dit is het hele blad, met alle vijftien verkopen. In G2 staat een prijsgrens van 10 euro.

=GEMIDDELDE.ALS(B2:B16;"Kleding";C2:C16)

De formule geeft 7,67 euro. GEMIDDELDE.ALS zoekt Kleding in kolom B, vindt zes rijen en deelt hun 46 euro door 6. Met "Huisraad" krijg je 8 euro: de lamp, de vaas en de boekensteun samen 24 euro, gedeeld door 3. Yasmina's vraag is dus beantwoord: huisraad is net iets duurder.

Probeer het zelf met een categorie die iets laat zien. De oefening draait op het echte blad van Tweede Kans, en je typt de formule zoals in Excel.

Je formule rekende in je eigen browser, op een echte rekenmotor. Daarna rekenen we ze nog eens op een verborgen versie van het blad, met andere prijzen, dus overtypen helpt niet.

Hoe gebruik je criteria in GEMIDDELDE.ALS?

Precies zoals bij SOM.ALS: tekst en tekens staan tussen aanhalingstekens, een cel plak je eraan met &, en jokertekens werken ook (Microsoft). Test je op dezelfde kolom als je middelt, dan mag het derde argument weg.

Het blad als geheel komt op 8,93 euro, met =GEMIDDELDE(C2:C16). Greet zit daar ruim boven, maar dat zegt weinig over haar: zij verkocht de kinderfiets. Zo'n vergelijking is pas eerlijk als je weet wat er achter het getal zit.

Hoe bereken je een gemiddelde met meerdere voorwaarden?

Met GEMIDDELDEN.ALS: de getallen vooraan, daarna de paren, net als bij SOMMEN.ALS. Alleen de rijen waar alle voorwaarden kloppen, tellen mee. =GEMIDDELDEN.ALS(C2:C16;B2:B16;"Kleding";E2:E16;"Sofie") geeft 6,33 euro: Jas groen, T-shirt XL en T-shirt M samen 19 euro, gedeeld door 3.

Tussen twee waarden gebruik je dezelfde kolom twee keer. =GEMIDDELDEN.ALS(C2:C16;C2:C16;">=5";C2:C16;"<=10") geeft 7 euro voor de vijf artikels van 5 tot en met 10 euro. Zo laat je ook uitschieters buiten beschouwing: =GEMIDDELDEN.ALS(C2:C16;B2:B16;"Speelgoed";C2:C16;"<"&G2) geeft 4,50 euro voor het speelgoed onder de grens. Probeer het zelf.

Hoe laat je nullen buiten het gemiddelde?

Met ">0" als criterium op de getallen zelf. Bij Zwembad De Plons hield beheerder Dirk de bezoeken van tien leden bij. Bij Lotte staat niets ingevuld, Sara kwam nog nooit. =GEMIDDELDE(D2:D11) geeft 9,78 bezoeken, want Sara's 0 telt mee en Lottes lege cel niet. =GEMIDDELDE.ALS(D2:D11;">0") geeft 11 bezoeken, het gemiddelde van wie echt kwam.

Welk getal klopt, hangt af van je vraag. Hoe vaak komt een lid gemiddeld? Dan hoort Sara erbij. Hoe vaak komt iemand die komt? Dan niet. Een cel die leeg is, slaat de functie sowieso over (Microsoft), dus let op het verschil tussen "niets ingevuld" en "echt nul". Meer daarover lees je in het artikel over GEMIDDELDE.

Waarom werkt mijn GEMIDDELDE.ALS niet?

Een gemiddelde kan op drie manieren misgaan: het geeft een fout, het rekent op de verkeerde rijen, of het klopt wiskundig maar misleidt. Die laatste is de gevaarlijkste, want Excel klaagt er niet over.

De formule geeft #DEEL/0!

Geen enkele rij voldoet aan de voorwaarde, of er staan geen getallen in de cellen die je middelt (Microsoft). Er werd geen meubel verkocht, dus =GEMIDDELDE.ALS(B2:B16;"Meubels";C2:C16) geeft #DEEL/0!. Dat is een eerlijk antwoord: er is geen gemiddelde. Wil je iets leesbaars tonen, schrijf dan:

=ALS.FOUT(GEMIDDELDE.ALS(B2:B16;"Meubels";C2:C16);"Geen verkoop")

Zet er geen 0 voor in de plaats: een gemiddelde van 0 euro is iets anders dan geen gemiddelde. Microsoft waarschuwt dat ALS.FOUT elke fout wegwerkt (Microsoft). Wanneer dat kwaad kan, lees je in ALS.FOUT en ALS.NB.

De bereiken zijn verschoven

GEMIDDELDE.ALS controleert de grootte van het laatste bereik niet: Excel begint bij de eerste cel en neemt een blok met dezelfde vorm als het eerste bereik (Microsoft). =GEMIDDELDE.ALS(B2:B16;"Kleding";C3:C16) geeft daardoor 7,33 euro in plaats van 7,67, en geen enkele melding. GEMIDDELDEN.ALS is strenger: elk criteriumbereik moet even groot zijn als het gemiddelde bereik, anders krijg je een fout (Microsoft).

Het gemiddelde van gemiddelden klopt niet

Neem je het gemiddelde van de vijf categoriegemiddelden, dan krijg je 9,57 euro, terwijl het echte gemiddelde 8,93 euro is. Elke categorie telt dan even zwaar, of ze nu één artikel heeft zoals Elektro of zes zoals Kleding.

Tel nooit gemiddelden op om er opnieuw het gemiddelde van te nemen. Reken terug naar de ruwe rijen, of weeg elk gemiddelde met zijn aantal.

Welke functies lijken op GEMIDDELDE.ALS?

De familie kent nu geen geheimen meer: dezelfde criteria, dezelfde paren, een andere berekening. Op het blad van Tweede Kans, voor de kleding van Sofie:

Wil je tellen, lees dan AANTALLEN.ALS met voorbeelden. Voor bedragen is er SOMMEN.ALS met voorbeelden. Elke functie staat met haar Engelse naam op het Excel-spiekbriefje. Wil je het allemaal zelf doen, volg dan module 4 van de Excel-cursus, en begin bij de gratis modules als je nog nieuw bent in Excel.