Met de functie GEMIDDELDEN.ALS bereken je het gemiddelde van een bereik, waarbij meerdere voorwaarden (criteria) tegelijk moeten gelden. Alleen de waarden die aan alle opgegeven criteria voldoen, worden meegenomen in het gemiddelde.
Schrijfwijze formule
=GEMIDDELDEN.ALS(gemiddelde_bereik; criteriumbereik1; criterium1; [criteriumbereik2; criterium2]; …)
Argumenten
| Gemiddelde_bereik | Dit is het bereik waarvan je het gemiddelde wilt berekenen. |
| Criteriumbereik1 | Hier voer je het bereik in waarop je het eerste criterium toepast. |
| Criterium1 | Hier voer je de voorwaarde in waaraan het criteriumbereik moet voldoen |
| Criteriumbereik2 / criterium2 (optioneel) | Optioneel kun je meerdere voorwaarden toepassen. Bij elke extra voorwaarde moet je opnieuw een criteriumbereik en criterium invoeren. |
Je kunt bij criteria gebruik maken van operatoren, zoals > (groter dan), < (kleiner dan), = (gelijk aan) of <> (niet gelijk aan), om voorwaarden te maken zoals ">10" of "=Amsterdam".
Voorbeeld 1: meerdere criteria op hetzelfde bereik
In het eerste voorbeeld maken we gebruik van hetzelfde voorbeeld als bij de functie GEMIDDELDE.ALS, zodat het verschil duidelijk zichtbaar is.
In kolom A staan producten en in kolom B staat de omzet. Met de formule =GEMIDDELDE.ALS(B2:B6;”>0″) bereken je het gemiddelde van alle bedragen groter dan 0 (dit is dus één criterium)
Met de functie uit dit artikel kun je echter meerdere criteria toepassen, bijvoorbeeld om grenzen te maken:
- criterium 1: omzet groter dan 0
- criterium 2: omzet kleiner dan 2000
De formule wordt dan: =GEMIDDELDEN.ALS(B2:B6;B2:B6;">0";B2:B6;"<2000")
De waarde die voldoen aan beide criteria zijn 1000, 1500 en 250.
Ter controle: (1000 + 1500 + 250) / 3 = 916,67
Toelichting van de formule:
- B2:B6 – dit is het bereik waarvan je het gemiddelde berekent
- B2:B6; “>0” – het eerste criterium (groter dan 0)
- B2:B6; “<2000” – het tweede criterium (kleiner dan 2000)
In dit voorbeeld is het gemiddelde_bereik hetzelfde als het criteriumbereik, en worden beide criteria op hetzelfde bereik toegepast. Maar je moet dus wel het het criteriumbereik twee keer expliciet opgeven.
Voorbeeld 2: criteria op verschillende bereiken
In dit voorbeeld is een extra kolom toegevoegd met de voorraad. We willen nu bijvoorbeeld de gemiddelde omzet berekenen van producten die:
- een voorraad groter dan 10 hebben
- een omzet kleiner dan 2000 hebben
De formule wordt nu: =GEMIDDELDEN.ALS(C2:C6;B2:B6;">10";C2:C6;"<2000")
De producten die aan beide criteria voldoen zijn:
- Stoelen: voorraad 30 en omzet 1500
- Lampen: voorraad 12 en omzet 250
Ter controle: (1500 + 250) / 2 = 875
Toelichting van de formule:
- C2:C6 – hier staat het gemiddelde_bereik (omzet)
- B2:B6; “>10” – het eerste criterium op de voorraad
- C2:C6; “<2000” – het tweede criterium op de omzet
Dit voorbeeld laat zien dat elk criterium zijn eigen criteriumbereik kan hebben, zolang de bereiken even groot zijn.
Voorbeeld 3: tekstcriterium combineren met een getal
In het laatste voorbeeld gaan we verschillende soorten criteriums toepassen. Hiervoor maken we gebruik van een overzicht van gewerkte uren van medewerkers.
We willen bijvoorbeeld het gemiddeld aantal gewerkte uren berekenen van:
- medewerkers van de afdeling Verkoop
- die meer dan 8 uur hebben gewerkt
De formule wordt dan: =GEMIDDELDEN.ALS(C2:C6;B2:B6;"Verkoop";C2:C6;">8")
Het resultaat is 9,5.
De volgende medewerkers voldoen aan beide voorwaarden:
- Peter: Verkoop en 9 uur.
- Mark: Verkoop en 10 uur.
Ter controle: (9 + 10) / 2 = 9,5
Toelichting van de formule:
- C2:C6 – Hier staat het bereik waarvan het gemiddelde wordt berekend (de gewerkte uren).
- B2:B6; “Verkoop” – Dit is het tekst criterium, waardoor alleen rijen in kolom B waar Verkoop staat voldoen.
- C2:C6; “>8” – Dit is het getal criterium, waardoor alleen de gewerkte uren groter dan 8 voldoen.
Pro tip:
Wil je dit soort formules duidelijker maken voor jezelf (en voor anderen), dan is het raadzaam om bereiken een naam te geven.
In het laatste voorbeeld kan dat bijvoorbeeld zo:
- Selecteer het bereik B2:B6 en geef het de naam Afdeling
- Selecteer het bereik C2:C6 en geef het de naam Uren (of Gewerkte_uren)
De formule wordt dan: =GEMIDDELDEN.ALS(Uren;Afdeling;"Verkoop";Uren;">8")
Door het gebruik van benoemde bereiken is bij het zien van de criteriumparen direct duidelijk wat de bedoeling is:
Afdeling; "Verkoop"Uren; ">8"
Criteria dynamisch maken met celverwijzingen
Je kunt er ook voor kiezen om de criteria niet vast in de formule te zetten, maar om gebruik te maken van celverwijzingen, bijvoorbeeld:
- In cel B8 voer je het criterium Afdeling in
- In cel B9 het criterium Gewerkte uren
De formule wordt dan: =GEMIDDELDEN.ALS(Uren;Afdeling;B8;Uren;B9)
Wanneer je de inhoud van deze cellen aanpast, wordt het resultaat van de formule automatisch bijgewerkt.
Als bijvoorbeeld cel B9 wordt aangepast naar >5, dan voldoet Stefan ook aan de voorwaarden en wordt het gemiddelde 9:
Ter controle: (8 + 9 + 10) / 3 = 9
Je kunt deze cellen (B8 en B9) eventueel ook een naam geven, zoals criterium_afdeling of criterium_uren.
De formule wordt daarmee nog explicieter, maar ook een stuk langer. Persoonlijk heeft dit daarom niet mijn voorkeur, maar het kan in sommige situaties wel handig zijn.
Weet je nooit welke formule je nodig hebt
in Excel?
In mijn cursus leer je stap voor stap hoe je formules opbouwt en combineert. Alles wordt op een laagdrempelige en gestructureerde manier uitgelegd, met praktische voorbeelden en oefeningen.
Je ontdekt hoe formules echt werken, zodat je precies weet wat je moet invullen. Na de cursus kun je met vertrouwen zelf formules maken, van eenvoudige berekeningen tot meer complexe toepassingen.