Gemiddelde berekenen van eerste kwartiel van cijfers die door elkaar staan

[carlocarlo]

Ik wil van een afdeling de gemiddelde productiviteit weten van mijn top 25%. Het probleem is dat de getallen niet van hoog naar laag oplopen. Daarnaast zijn een aantal velden leeg. Is er een (standaard) formule waarmee ik die berekening kan maken. Voor het gemak heb ik er een bestand aan toegevoegd.

 

[arrie23]

Werk je voorbeeld eens iets verder uit. Wat zou er uit moeten komen? Tellen lege waarden wel/of niet mee bij het bepalen van de top 25%

 

[zapatr]

Moeten de lege cellen als 0 worden aangemerkt of moeten ze niet worden meegeteld?
Als ze niet moeten worden meegeteld, zijn er dus geen 16 maar 14 waarden die voor de berekening meetellen.

 

[carlocarlo]

toelichting op mijn vraag

Lege cellen tellen niet mee. In mijn voorbeeld zou het er inderdaad op neer komen dat er 14 cellen gevuld zijn. Deel je dat door 4 dan kom je op 3,5 cel. Als het mogelijk is dan zou ik dit naar boven willen laten afronden. Dus dan krijg ik de gemiddelde waarde van de top 25% over 4 cellen met de hoogste waarde. Pak ik de kolom uit de bijlage dan zal de uitkomst moeten zijn:
- de top 25% (naar boven afgerond) uit de kolom is : 55, 16, 13 en 11.
- daar de gemiddelde waarde van is : 23,75.

 

[zapatr]

Excel heeft een ingebouwde functie voor kwartielen: =Kwartiel(bereik;kwartielnr).

 

[sylvester-ponte]

carlo, excel kent de formule KWARTIEL .
daar kun je mee experimenteren zo veel je wil.
deze formule houd rekening met lege cellen en sortering doet hij intern. probeer zelf maar uit.

 

[zapatr]

@carlo,
De functie kwartiel zal voor jouw doeleinden waarschijnlijk niet (altijd) voldoen. Dat hangt samen met hoe die functie is samengesteld. Je kunt wel een andere benadering gebruiken.
Het aantal waarden dat je in acht moet nemen (hier 4), kun je (als het getallen zijn) berekenen met =afronden(aantal(a1:a16)/4;0). vervolgens kun je dan gebruiken:

=GEMIDDELDE(GROOTSTE(A1:A16;{1;2;3;4}))

De uitkomst is dan die je wenste, nl. 23,75 .

 

[carlocarlo]

carlo, excel kent de formule KWARTIEL .
daar kun je mee experimenteren zo veel je wil.
deze formule houd rekening met lege cellen en sortering doet hij intern. probeer zelf maar uit.

Hallo Sylvester,

Bedankt voor je reactie.
Als ik die functie gebruik dan komt er het getal 6 uit. De formule ziet er nu zo uit: =KWARTIEL(A1:A16;1)
Wat heb ik verkeerd gedaan.

 

[carlocarlo]

Hallo Zapatr,

Bedankt voor je reactie. et de laatste formule krijg ik inderdaad 23,75 er uit.
Bij de standaard functie krijg ik het getal 6. Zie ook mijn opmerking richting Sylvester. Ik weet niet precies wat ik nu verkeerd doe

 

[zapatr]

@carlo,
dan heb je waarschijnlijk het eerste kwartiel berekend.

 

[carlocarlo]

@carlo,
dan heb je waarschijnlijk het eerste kwartiel berekend.

Klopt, het is de eerste keer dat ik met deze formule werk. wat had ik in moeten vullen om 23,75 te kunnen krijgen?

 

[zapatr]

Een korte uitleg, want ten eerste kost me een volledige uitleg te veel tijd en ten tweede is over dit onderwerp (zoals over de meeste onderwerpen) op internet genoeg te vinden.
Afhankelijk van de definitie van kwartielen, bestaan daarover verschillende berekeningen.
Waarschijnlijk heb je al geprobeerd =kwartiel(a1:a14;4) met als uitkomst 55 dat is niet het resultaat dat je wilde.
Je hebt (als alle waarden van laag naar hoog gesorteerd zijn):
1. de mediaan die de middelste waarde geeft van alle gegevens (of het gemiddelde van de twee middelste waarden als het aantal waarden even is); dat is het 2e kwartiel.
2. de middelste waarde van het gedeelte waarvan de waarden beneden de mediaan liggen (1e kwartiel).
3. de middelste waarde van het gedeelte waarvan de waarden boven de mediaan liggen (3e kwartiel).
Bij 2 en 3 geldt ook weer dat bij een even aantal waarden het gemiddelde van de twee middelste waarden moet worden genomen.
4. de laatste waarde (4e kwartiel) = 55.

 

[carlocarlo]

Beste ZAPATR,

Bedankt voor je toelichting. Ik zal je zeker niet afschieten Het is mij duidelijk.

met vriendelijke groet,
Carlo

 

[Piet Bom]

Deze formule doet het volgens mij:

=SOM.ALS($A$1:$A$9999;">="&KWARTIEL($A$1:$A$9999;3);$A$1:$A$9999)/AANTAL.ALS($A$1:$A$9999;">="&KWARTIEL($A$1:$A$9999;3))

 

[Leotaxi]

misschien zo dan ?

 

[zapatr]

@Leo,
Je gaat er in je oplossing van uit dat het aantal waarden dat je in de berekening moet betrekken (4) al bekend is, maar dat is niet zo. Ik berekende dat eerst met aantal.als en gebruikte dat daarna in mijn berekening (zie bericht #7).
@Piet,
jij berekent het gemiddelde van de waarden die groter dan- of gelijk zijn aan het derde kwartiel, ook een goede benadering. Wat korter wordt dat:

=GEMIDDELDE.ALS(A1:A16;">"&KWARTIEL(A1:A16;3))

Zoals gezegd: elke oplossing blijft een benadering. Excel geeft als waarde van het derde kwartiel bv.10,5 terwijl het met deze waarden 11 is. Nu gaat het in dit topic om een zeer klein aantal gegevens en dan kan 1 waarde meer of minder al meteen een grote invloed hebben. Doorgaans hebben statistische berekeningen plaats over een (zeer) groot aantal gegevens en dan speelt 1 waarde meer of minder meestal geen grote rol.

 

[Piet Bom]

@zapatr,
Ik werk thuis met Excel2003, die kent gemiddelde.als nog niet.
Vandaar dat ik met som.als en aantal.als werk.
Dat is wel wat langer.
Ik denk overigens dat het "Groter dan of gelijk aan" moet zijn.

=GEMIDDELDE.ALS(A1:A16;">[COLOR="#FF0000"]=[/COLOR]"&KWARTIEL(A1:A16;3))

 

[zapatr]

@Piet,
Of je "groter dan-" dan wel "groter dan of gelijk aan" wil berekenen, dat moet de vragensteller zelf beslissen. Er zijn 7 waarden die groter zijn dan de mediaanwaarde. Van die 7 is 11 de mediaan (derde kwartiel). De vraag is dan, wil je:
1. het gemiddelde berekenen van alle waarden die goter zijn dan de derde kwartielwaarde, of
2. wil je die laatste waarde (derde kwartielwaarde) er ook bij betrekken?
Op grond van zijn eerste bericht denk ik dat de vragensteller 1 wil, maar zeker weet ik dat niet. Misschien doen er die kwartielen voor hem er niet meer zo toe en neemt hij het gemiidelde van de 4,5,6, of 10 hoogste waarden.
Ik vind overigens dat ik nu genoeg tijd aan dit topic heb besteed.

 

[Leotaxi]

@ Zapatr

in mijn voorbeeld enkel naam fout gekozen "top 4" dit moet zijn top 25 %
het delen door 4 gebeurd met het totaal gevonden waarden,
het gemiddelde in mijn voorbeeld wordt dus op 9 gevonden waardes berekend
wat ik dacht dat dat de bedoeling van vraagsteller was.

 

[zapatr]

Ik werk thuis met Excel2003, die kent gemiddelde.als nog niet.
Vandaar dat ik met som.als en aantal.als werk.

In Excel 2003 en eerder kun je ook gebruiken (in te voeren via Ctrl-Shift-Enter):

=gemiddelde(als(a1:a16>kwartiel(a1:a16;3);a1:a16))