[Excel] mogelijk om selectief gemiddeldes laten optellen?

[hejwazzup]

Beste,

Jullie hebben mij in het verleden geweldig geholpen, dus ik blijf terugkomen met mijn vragen.
Ik probeer mijn ‘probleem’/’wens’ zo duidelijk mogelijk te omschrijven.

Ik werk op een school waar wij in Excel de toetsresultaten bijhouden. In het verleden heb ik door middel van jullie hulp een Excel bestand gemaakt dat perfect werkt. Mijn collega’s en ik kunnen gemakkelijk de cijfers invullen en de beoordeling wordt automatisch door Excel berekend.

We lopen alleen tegen één probleem aan. Twee keer per jaar krijgen de leerlingen een rapport mee.
Voor het eerste rapport tellen de eerste x aantal toetsen mee en voor het twee rapport de laatste x aantal toetsen. Het enige wat ik in mijn huidige bestand voor elkaar heb gekregen is dat ik een totaal gemiddelde kan laten berekenen.
Maar wat ik heel graag zou willen is dat ik kan aangeven welke toetsen voor welk rapport (en dus voor welk gemiddelde) tellen en dus Excel meeneemt in z’n gemiddelde berekening.

Ik denk zelf dat dit mogelijk is door middel van een =gemiddeldeALS formule waarin ik in bijvoorbeeld een rij door middel van 0 en 1 kan aangeven welke toets Excel wel en niet moet meenemen om het gemiddelde uit te rekenen. Het lukt mij echter niet om dit voor elkaar te krijgen.

Wellicht dat het jullie wel lukt!
Ik heb een screenshot ter verduidelijking en voor de mensen die graag een werkbestand willen gebruiken heb ik op de volgende locatie een test bestand geüpload.



Alvast enorm bedankt!

P.S. Ik ben in het verleden -terecht- erop gewezen dat een bestand uit Dropbox niet handig werkt op een forum. In dit geval heb ik geen idee welke formules ik zou moeten gebruiken dus kan ik geen formules weergeven. Ik zal uitgebreid de formules in dit forum beschrijven als ik geholpen ben en de Dropbox link verwijderen.
Het plaatje heb ik geüpload voor de mensen die niet de Dropboxfile willen open.

[begintmeta]

ZIjn de kolommen binnen het x-aantal voor rapport 1 en 2 hetzelfde voor alle leerlingen? Ik zou het dan voordehandliggend vinden om twee gemiddelden uit te rekenen, voor de kolommen rapport 1, voor de kolommen rapport 2 (en desgewenst voor het totaal over alle kolommen, dan dus drie gemiddelden). Je kan zoiets ook makkelijk conditioneel maken (dus baseren op een kolom die aangeeft bij welk rapport de toets hoort) als dat makkelijker zou zijn (met IF, AND etc).

[ Voor 29% gewijzigd door begintmeta op 06-06-2014 10:18 ]

[hejwazzup]

begintmeta schreef op vrijdag 06 juni 2014 @ 10:12:
ZIjn de kolommen binnen het x-aantal voor rapport 1 en 2 hetzelfde voor alle leerlingen? Ik zou het dan voordehandliggend vinden om twee gemiddelden uit te rekenen, voor de kolommen rapport 1, voor de kolommen rapport 2 (en desgewenst voor het totaal over alle kolommen, dan dus drie gemiddelden). Je kan zoiets ook makkelijk conditioneel maken (dus baseren op een kolom die aangeeft bij welk rapport de toets hoort) als dat makkelijker zou zijn (met IF, AND etc).

Bedankt voor je reactie.
Welke toetsen meegerekend worden voor het rapport is per jaar/per klas verschillend. Vandaar dat ik niet vast kan instellen dat bijvoorbeeld kolom (toets) 1 t/m 3 voor rapport 1 gelden en kolom (toets) 4 t/m 6 voor rapport 2 gelden.

Ik zat zelf te denken aan drie kolommen van gemiddeldes.
Kolom 1 voor rapport 1
Kolom 2 voor rapport 2
Kolom 3 voor een totaalgemiddelde.

Het totaalgemiddelde heb ik al voor elkaar, maar de andere twee niet.

Ik weet niet of het volgende -makkelijk- voor elkaar te krijgen is:

Bijvoorbeeld een rij, tabel waarin alle toetsen staan. met daarachter een vakje waar ik een 0 of een 1 in kan zetten. Indien bij de rij van rapport 1 een 1 bij een toets staat berekent Excel wel deze toets mee in het gemiddelde en als er een 0 staat dan berekent Excel deze toets niet mee in het gemiddelde. Het lijkt mij dat dit mogelijk is met een ALS/IF formule, maar mij lukt het niet,

Zie dit voorbeeld ter verduidelijking:


Maar misschien is mijn idee helemaal niet haalbaar of niet het beste idee.

[ Voor 5% gewijzigd door hejwazzup op 06-06-2014 11:32 ]

[Orion84]

Het lijkt mij dat dit mogelijk is met een ALS/IF formule, maar mij lukt het niet,

Dat lijkt mij ook de juiste richting. Maar zonder wat concretere uitleg wat je geprobeerd hebt is het wat lastig om je daar op weg te helpen (behalve dan door kant en klare oplossingen voor te kauwen, maar dat is niet echt het idee van een discussieforum).

[Sjeik]

Kan je niet met som.als delen door aantal.als? Volgens mij heb je dan iets gemaakt als gemiddelde.als?

[Dido]

Sjeik schreef op vrijdag 06 juni 2014 @ 11:30:
Kan je niet met som.als delen door aantal.als? Volgens mij heb je dan iets gemaakt als gemiddelde.als?

De grote vraag is de als, het gemiddelde is niet zo lastig denk ik

[Sjeik]

Zoiets?
Rij 3 en 4 kan je verbergen. Ook kan je formule die de normering ophaalt een stuk eenvoudiger. In mijn bestand kan je in cel D7 de volgende formule gebruiken:
=VERT.ZOEKEN(C7;$AL$7:$AO$11;4;WAAR)
Deze kan je naar de andere velden kopiëren.

[ Voor 69% gewijzigd door Sjeik op 06-06-2014 12:08 ]

[hejwazzup]

Sjeik schreef op vrijdag 06 juni 2014 @ 11:53:
Zoiets?
Rij 3 en 4 kan je verbergen. Ook kan je formule die de normering ophaalt een stuk eenvoudiger. In mijn bestand kan je in cel D7 de volgende formule gebruiken:
=VERT.ZOEKEN(C7;$AL$7:$AO$11;4;WAAR)
Deze kan je naar de andere velden kopiëren.

Het werkt echt perfect. Bedankt.
Het enige probleem is dat als een leerling nog geen cijfer heeft er standaard 0% staat. (en die 0% neemt hij ook mee in het gemiddelde). Kan dit ook anders, dus als een leerling geen cijfer heeft dat er geen 0%, maar gewoon niks komt te staan, dus dat hij dan deze cel niet meerekent?



Ik zal na deze vraag uitgebreid deze formules in het forum beschrijven.

[ Voor 8% gewijzigd door hejwazzup op 07-06-2014 10:17 ]

[Rupie]

Wat heb je al bedacht waarmee je dit zou kunnen doen dan? Wat kom je tegen als je zoekt naar oplossingen om de waarde 0 te onderdrukken?

[hejwazzup]

Rupie schreef op vrijdag 06 juni 2014 @ 21:59:
Wat heb je al bedacht waarmee je dit zou kunnen doen dan? Wat kom je tegen als je zoekt naar oplossingen om de waarde 0 te onderdrukken?

code:

=ALS(C10="";"";SOMMEN.ALS(B10:AB10;$B$3:$AB$3;1)*0,1/AANTALLEN.ALS($B$3:$AB$3;1))

Dit heb ik geprobeerd en dat werkt. Maar dan werkt het alleen voor de eerste toets. Zodra ik ipv C10 bijvoorbeeld B10:AB10 opgeef krijg ik een #WAARDE! fout.

[ Voor 34% gewijzigd door hejwazzup op 07-06-2014 10:37 ]

[hejwazzup]

Los van het feit dat hij ook het gemiddelde uitrekent als er niks is ingevuld (wat ik nog niet eens zo erg vind, maar wat er gewoon iets minder mooi uitziet), werkt de formule van Sjeik ook nog niet volledig kom ik nu achter.

De formule werkt goed mits de totaal aantal te behalen punten 10 is, maar -en dat ben ik zelf vergeten te zeggen- dit aantal kan per toets veranderen. Zodra ik bijvoorbeeld 100 bij de totaal aantal te behalen punten invoer, werkt de formule niet meer.
Dit komt -zo lijkt het in mijn ogen- doordat de formule niet het percentage pakt om mee te rekenen, maar de toetsscore. Is dit ook nog te veranderen? Ik ben al uren aan het puzzelen maar krijg het niet voor elkaar om ervoor te zorgen dat hij niet de toetsscore pakt, maar het percentage voor het gemiddelde.

Ook bij bijvoorbeeld deze formule -die hetzelfde lijkt te doen als de formule van Sjeik-, pakt hij de toetsscores ipv het bijbehorende percentage voor het gemiddelde percentage.

code:

=GEMIDDELDEN.ALS(B7:AB7;$B$3:$AB$3;1)*0,1

Excuus, ik zie nu dat ik als laatste had gereageerd. Ik had mijn laatste post moeten aanpassen ipv een nieuwe posten.


/OPGELOST.
Ik heb een paar kolommen naar rechts steeds verwijzingen gemaakt naar de cellen waarin percentages werden berekend en daar de volgende formules op losgelaten:

code:

=GEMIDDELDEN.ALS(AP11:AX11;$AP$6:$AX$6;1)

Waardoor mijn probleem ook direct is opgelost wanneer een leerling geen score heeft dat deze niet mee berekend wordt.



Bedankt voor jullie hulp.

[ Voor 31% gewijzigd door hejwazzup op 07-06-2014 11:33 ]