Hoe bereken je een gemiddelde in Excel? Ook zonder nullen, met een voorwaarde of gewogen

Hoe bereken je een gemiddelde in Excel?

Typ in een lege cel =GEMIDDELDE(, selecteer de cellen met de getallen, sluit het haakje en druk op Enter. Excel telt de getallen op en deelt door hoeveel het er zijn: het gemiddelde van 4, 8 en 9 is 21 gedeeld door 3, dus 7. Het kan ook zonder typen. Klik onder je kolom, kies op het tabblad Start de pijl naast AutoSom en klik op Gemiddelde (Microsoft).

=GEMIDDELDE(B2:B9)

Het is geen zeldzame functie: Microsoft noemde SOM en GEMIDDELDE in 2019 de twee meest gebruikte functies van Excel (Microsoft). Toch loopt het vaak mis, omdat een lege cel, een 0 en een getal als tekst elk anders meetellen. Hieronder zie je hoe dat zit, op echte bladen uit onze Excel-cursus. Wil je eerst de basis oefenen, begin dan bij de les het gemiddelde met GEMIDDELDE.

Hoe werkt de functie GEMIDDELDE?

=GEMIDDELDE(getal1; [getal2]; ...)

Alleen getal1 is verplicht: het eerste getal, de eerste cel of het eerste bereik. Daarna mag je er meer opgeven, volgens Microsoft tot 255 (Microsoft), en elk argument mag een heel bereik zijn. =GEMIDDELDE(D2:D11;F2:F11) neemt twee kolommen samen, =GEMIDDELDE(D2:E11) een blok van twee kolommen breed. De functie werkt dus over rijen en kolommen heen, net als SOM.

In een Engelstalige Excel heet de functie AVERAGE. Het teken tussen de argumenten hangt niet af van Windows of Mac, maar van de landinstellingen van je besturingssysteem en van Excel (Microsoft). In België en Nederland is de komma het decimaalteken, dus staat er meestal een puntkomma. Een formule van een Engelstalige site, zoals =AVERAGE(D2:D11,F2:F11), wordt in jouw Excel dan =GEMIDDELDE(D2:D11;F2:F11).

Hoe bereken je het gemiddelde van een kolom?

Neem de hele kolom als bereik, ook de lege cellen: GEMIDDELDE slaat wat leeg is zelf over. Een voorbeeld. Ria, de voorzitter van volkstuinen De Groene Vinger in Hasselt, weegt elk jaar de oogst van tien percelen. De Groene Vinger is de tweede klant in onze Excel-cursus. In kolom D staan de kilo's tomaten. Perceel P04 is niet verhuurd, en Luc kweekte op P09 geen tomaten: die twee cellen zijn leeg.

=GEMIDDELDE(D2:D11)

De formule geeft 18,4375, en in een kolom met twee decimalen toont Excel 18,44 kilo. Dat is 147,5 kilo gedeeld door 8, niet door 10: de twee lege cellen slaat GEMIDDELDE over. Reken je het zelf uit met =SOM(D2:D11)/10, dan kom je op 14,75 kilo, alsof P04 en P09 elk 0 kilo tomaten oogstten. Maar een perceel zonder tomaten heeft geen slechte tomatenoogst. Het heeft er gewoon geen.

Probeer het nu zelf, op het echte blad van De Groene Vinger, met de courgettes en de bonen erbij. Eén formule volstaat. Als je ze naar rechts doortrekt, schuift het bereik mee naar de volgende kolom, en de lege cellen in elke kolom slaat GEMIDDELDE vanzelf over.

Je formule rekende in je eigen browser, en we controleerden ze daarna op een tweede, verborgen versie van het blad, met andere oogsten. Typ je 18,44 in plaats van een formule, of deel je zelf door 10, dan klopt het hier misschien en valt het daar door de mand.

Tellen lege cellen, nullen en tekst mee in een gemiddelde?

Lege cellen niet, nullen wel, tekst niet. Microsoft schrijft dat GEMIDDELDE tekst, logische waarden en lege cellen in een bereik negeert, maar dat cellen met de waarde 0 wel meetellen (Microsoft). Dat verschil zie je goed op de ledenlijst van zwembad De Plons in Turnhout, de derde klant in de cursus.

=GEMIDDELDE(D2:D11) geeft 9,78 bezoeken: 88 gedeeld door 9. Bij Lotte is niets ingevuld, dus telt ze niet mee. Sara kwam geen enkele keer, en die 0 telt wel mee. Dat klopt ook: Sara heeft een jaarabonnement en kwam echt niet. Typt iemand bij Lotte een 0 omdat hij het niet weet, dan zakt het gemiddelde naar 8,8, terwijl niemand minder kwam.

Bij de leeftijden geeft =GEMIDDELDE(B2:B11) 36,44 jaar: 328 gedeeld door 9. De 12 van Hanne staat als tekst in de cel en wordt zonder foutmelding overgeslagen. Als getal zou het gemiddelde 34 jaar zijn. Twijfel je, vergelijk dan =AANTAL(B2:B11) met =AANTALARG(B2:B11): 9 getallen tegenover 10 gevulde cellen. Hoe AANTAL telt, oefen je in de les tellen met AANTAL.

Een nul die je niet ziet, telt toch mee. Zet je in de opties van Excel "Een nul weergeven in cellen met een nulwaarde" uit, dan lijkt een cel met 0 leeg, maar GEMIDDELDE rekent ze gewoon mee (Microsoft).

Hoe bereken je een gemiddelde zonder nullen of met een voorwaarde?

Met GEMIDDELDE.ALS, dat alleen de getallen meeneemt die aan een voorwaarde voldoen. Microsoft geeft zelf het voorbeeld zonder nullen, met "<>0" als voorwaarde: niet gelijk aan nul (Microsoft). Bij De Plons geeft dit 11 bezoeken, 88 gedeeld door 8, zonder Sara en zonder de lege cel van Lotte. Kies het alleen als die nullen echt niet mogen meetellen.

=GEMIDDELDE.ALS(D2:D11;"<>0")

GEMIDDELDE.ALS heeft drie argumenten: het bereik met de voorwaarde, de voorwaarde, en het bereik met de getallen. Laat je het derde weg, dan neemt Excel het gemiddelde van het eerste bereik (Microsoft). Met alle drie de argumenten rekent kringwinkel Tweede Kans in Gent, de vierde klant in de cursus, uit wat een stuk kleding deze week gemiddeld opbracht.

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

De eerste formule geeft 7,67 euro: zes stuks kleding voor samen 46 euro. Met meer dan één voorwaarde neem je GEMIDDELDEN.ALS, en dan staat het bereik met de getallen vooraan (Microsoft). De tweede formule geeft 12 euro, het gemiddelde van de kleding boven 5 euro. Dezelfde voorwaarden schrijf je ook bij optellen met SOM.ALS en tellen met AANTAL.ALS. Nu jij, met het speelgoed.

Gemiddelde of mediaan: welke kies je?

Kies de mediaan als een paar uitschieters je gemiddelde scheeftrekken. De mediaan is het middelste getal als je alles sorteert, en een uitschieter trekt daar nauwelijks aan. In de kringwinkel kost een artikel gemiddeld 8,93 euro, maar tien van de vijftien artikels kosten minder. De kinderfiets van 35 euro trekt het gemiddelde omhoog.

=GEMIDDELDE(C2:C16)
=MEDIAAN(C2:C16)

De mediaan is 6 euro: zeven artikels kosten minder, zeven meer. Laat je de fiets weg, dan zakt het gemiddelde naar 7,07 euro en de mediaan maar naar 5,5, want bij een even aantal getallen neemt MEDIAAN het gemiddelde van de twee middelste. Volgens Microsoft zijn gemiddelde en mediaan gelijk als de getallen symmetrisch verdeeld zijn, en kunnen ze verschillen als de verdeling scheef is (Microsoft).

Welke je toont, hangt af van je vraag. Wil je weten wat een week opbrengt, dan heb je het gemiddelde nodig, want gemiddelde maal aantal is het totaal. Wil je weten wat een gewoon artikel kost, dan zegt de mediaan meer. Reken ze bij twijfel allebei uit: liggen ze ver uit elkaar, dan zit er een uitschieter in je gegevens.

Hoe bereken je een gewogen gemiddelde in Excel?

Vermenigvuldig elk getal met zijn gewicht, tel die producten op en deel door de som van de gewichten. GEMIDDELDE geeft elk getal evenveel gewicht, en dat is niet altijd wat je zoekt. Neem de zaterdagavond van frituur 't Gouden Frietje in Aalst, de eerste klant in de cursus.

=GEMIDDELDE(B2:B9) geeft 2,79 euro, de gemiddelde prijs op de menukaart. Maar wat betaalde een klant gemiddeld per verkocht product? Dan moet de mayonaise, die 70 keer over de toonbank ging, zwaarder wegen dan het water, dat 22 keer verkocht werd.

=SOM(B2:B9*C2:C9)/SOM(C2:C9)

Dat geeft 2,56 euro: 919,20 euro omzet gedeeld door 359 verkochte producten. In Microsoft 365 bevestig je die formule gewoon met Enter. In oudere versies is het een matrixformule, die je met Ctrl+Shift+Enter bevestigt (Microsoft). Veel mensen nemen SOMPRODUCT, zoals Microsoft in zijn eigen voorbeeld van een gewogen gemiddelde (Microsoft): =SOMPRODUCT(B2:B9;C2:C9)/SOM(C2:C9).

Wil je elke stap zien, gebruik dan een hulpkolom. Zet in D2 =B2*C2, trek door tot D9 en deel met =SOM(D2:D9)/SOM(C2:C9). Dat werkt in elke versie, en je ziet meteen welk product het meest opbracht: de kleine friet, met 179,20 euro.

Hoe rond je een gemiddelde af?

Met AFRONDEN: =AFRONDEN(GEMIDDELDE(D2:D11);1) maakt van de 18,4375 kilo tomaten echt 18,4. Minder decimalen tonen met de knoppen van de opmaak is iets anders. Dan ziet de cel er afgerond uit, maar rekent elke formule die ernaar verwijst verder met 18,4375. Het tweede argument is het aantal cijfers na de komma, en met 0 krijg je hele kilo's. Dat verschil oefen je in de les afronden met AFRONDEN.

Waarom geeft GEMIDDELDE #DEEL/0!?

Omdat er niets te middelen valt. Een gemiddelde is een som gedeeld door een aantal, en zonder één getal deel je door nul. Excel toont #DEEL/0! als een getal door nul gedeeld wordt (Microsoft). Bij GEMIDDELDE gebeurt dat als het bereik leeg is of alleen tekst bevat. GEMIDDELDE.ALS geeft het ook als geen enkele cel aan de voorwaarde voldoet (Microsoft).

In de kringwinkel werd deze week geen enkel meubel verkocht, dus =GEMIDDELDE.ALS(B2:B16;"Meubels";C2:C16) geeft #DEEL/0!. Dat is eigenlijk een eerlijk antwoord: er is geen gemiddelde. Wil je op het blad toch een tekst zien, vang dat ene geval dan op met ALS en AANTAL.ALS:

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

ALS.FOUT is korter, maar Microsoft waarschuwt dat het alle fouten onderdrukt, niet alleen #DEEL/0! (Microsoft). Een tikfout in de formule zie je dan ook niet meer. Met ALS vang je alleen het geval waarin er niets te middelen valt. Hoe ALS kiest tussen twee uitkomsten, lees je in de functie ALS uitgelegd.

Waarom klopt mijn gemiddelde niet?

Meestal rekent GEMIDDELDE precies wat je vraagt, maar staat er in het bereik iets anders dan je denkt. Een foutmelding krijg je dan niet: het getal ziet er geloofwaardig uit en is toch fout. Loop deze vier oorzaken na voor je het doorstuurt:

• Een 0 waar niets hoort. Een onbekende waarde laat je leeg. Typt iemand bij Lotte een 0, dan zakt het gemiddelde bij De Plons van 9,78 naar 8,8 bezoeken.
• Getallen als tekst. GEMIDDELDE slaat ze zonder foutmelding over, zoals de leeftijd van Hanne. Selecteer de cellen, klik op het waarschuwingsknopje dat erbij verschijnt en kies Converteren naar getal (Microsoft).
• Verborgen rijen. GEMIDDELDE rekent met elke cel van het bereik, ook in rijen die je verborg of wegfilterde. Voor alleen wat zichtbaar is, neem je =SUBTOTAAL(101;D2:D11). Met 101 slaat SUBTOTAAL ook rijen over die je zelf verborg, en wat een filter verbergt, laat het altijd weg (Microsoft).
• Een gemiddelde van gemiddelden. In de kringwinkel kost kleding gemiddeld 7,67 euro, boeken 2,50, huisraad 8, speelgoed 14,67 en elektro 15. Het gemiddelde van die vijf is 9,57 euro, terwijl een artikel gemiddeld 8,93 kostte: de ene radio weegt dan even zwaar als zes stuks kleding. Neem het gemiddelde van de oorspronkelijke getallen.

Welke functies horen bij GEMIDDELDE?

De beste en de slechtste oogst van De Groene Vinger zoek je met MIN en MAX, in de les de grootste en de kleinste. En wil je de andere formules kennen die je elke week tegenkomt, lees dan de belangrijkste Excel-formules voor beginners.

Het gemiddelde is de eerste les van module 2 in onze Excel-cursus, bij De Groene Vinger. Je rekent er op echte bladen, in de taal en met het scheidingsteken van jouw Excel, en elke formule wordt gecontroleerd op haar uitkomst. De eerste drie modules zijn gratis.