[Excel 2013] ALS.FOUT GEMIDDELDE en MAX bij geen waarde "-"?

[Arunia]

Ik ben bezig met een oplossing waarbij cijferlijsten in Excel ingevuld kunnen worden en waar dan ook gemiddelden berekend kunnen worden.
Nu is er van elke toets een Herkansing mogelijk, maar hiervan moet alleen het hoogste cijfer gebruikt worden voor het gemiddelde.

Dat lukt allemaal prima. Wat me niet lukt, is om een streepje te krijgen wanneer er niets nog is ingevuld in de cellen.
Los van elkaar werkt het prima, maar wanneer ik bovenstaande ga combineren krijg ik altijd 0,0 ipv een streepje.

Mijn formule op dit moment:

XML:

=GEMIDDELDE(MAX(F2:G2);MAX(H2:I2);MAX(J2:K2))

Wat er gebeurd, is dat de hoogste waarde van F2 en G2 gepakt wordt, idem voor H2 en I2 en J2 en K2.
Daarna het gemiddelde van die 3 cijfers.
Alleen, nu wordt er wanneer geen van die 6 cellen een waarde bevat, 0,0 neer gezet.

Voorheen had ik alleen de GEMIDDELDE EN DE FOUT.ALS formule gecombineerd, maar dan krijg je het totale gemiddelde.

De totale formule welke ook niet werkt:

XML:

=ALS.FOUT(GEMIDDELDE(MAX(F2:G2);MAX(H2:I2);MAX(J2:K2));"-")

Alleen hier blijft er hoe dan ook 0,0 bestaan.

Google biedt geen soelaas.
Mis ik ergens iets? Klopt mijn formule niet, of is het gewoon logisch dat hij standaard 0,0 weer geeft.
Hebben jullie wellicht een idee?

[naitsoezn]

Kun je niet gewoon controleren of het gemiddelde groter is dan nul, en zo niet: het streepje neerzetten?

Edit: Heb alleen de Engelse versie hier, maar zoiets zou toch moeten werken?

code:

=IF(AVERAGE(MAX(F2:G2),MAX(H2:I2), MAX(J2:K2))=0, "-", AVERAGE(MAX(F2:G2),MAX(H2:I2), MAX(J2:K2)))

[ Voor 48% gewijzigd door naitsoezn op 01-12-2014 14:04 ]

[Solomon]

Ik zou proberen niet teveel tegelijk te doen. Sowieso is dat naar mijn mening een best-practice in Excel, aangezien je daarmee veel onduidelijkheid/risico op fouten uit de weg gaat.

Ik zou een nieuwe kolom toevoegen, en daarin het maximum van ieder proefwerk wordt berekent:

code:

=IF(MAX(F2:G2)=0;"";MAX(F2:G2))

Vervolgens kun je het gemiddelde van deze cellen berekenen. Als er nog geen cijfers ingevuld zijn is het resultaat van bovenstaande formule een lege cel welke niet in de gemiddelde meegenomen wordt. Je zou zelfs de kolom kunnen verbergens als je deze hinderlijk vindt.

[Verwijderd]

Arunia schreef op maandag 01 december 2014 @ 13:58:
Wat me niet lukt, is om een streepje te krijgen wanneer er niets nog is ingevuld in de cellen. Los van elkaar werkt het prima, maar wanneer ik bovenstaande ga combineren krijg ik altijd 0,0 ipv een streepje.

Met "nog niets ingevuld" bedoel je, neem ik aan, dat er nog geen enkele cel van F2:K2 is ingevuld.
Als dat het geval is, gebruik dan dit:

code:

=ALS(AANTAL(F2:K2)>0;GEMIDDELDE(MAX(F2:G2);MAX(H2:I2);MAX(J2:K2));"-")

En let op: het is niet "gemiddeld" maar gemiddelde

[Arunia]

Hartelijk dank allen.

Het liefst doe ik alles (wanneer mogelijk uiteraard) in 1 cel.

alfas, daar heb je gelijk in, was een typo van mij.
Inderdaad, de cellen zijn nog leeg

Zowel de oplossing van naitsoezn en alfas werken wbt het streepje, maar zodra er minder dan 3 cellen ingevuld zijn, dan deelt hij deze alsnog door 3.
Ofwel, cel F en H staat 2,0 in. Dan is het gemiddeld 1,3. Dit zou 2,0 moeten zijn omdat cellen J en K nog niet ingevuld zijn.
Het vreemde is, dat eigenlijk het Gemiddelde gedeelte hetzelfde is, met alleen een stukje formule ervoor (ALS).
Ik heb al haakjes om de formule voor het GEMIDDELDE gezet, maar dat voegt niets toe.

Ik zie overigens dat de losse gemiddelde berekening ook als uitkomst 1,3 geeft.

Ik zie nu net dat de formule met MAX niet lekker werkt. Hierdoor komt je automatisch op de verkeerde waarde uit omdat Excel dit als reden gebruikt om altijd te delen door 3, terwijl de andere cellen niet ingevuld zijn.
Denk dat ik of Solomon's oplossing moet gebruiken of op zoek moet naar een formule waar dit niet fout gaat.
Ga ook meteen even experimenteren met Solomon's idee.

[Verwijderd]

Als je nou eens precies aangeeft WAT er WANNEER berekend moet worden, dan lijkt mij een oplossing snel beschikbaar. Want dat is mij nl. niet helemaal duidelijk.
Wanneer moet het gemiddelde van twee getallen wel meegenomen worden en wanneer niet?
Bv: als F1 wel is ingevuld en G2 niet, moet dan F2 wel meetellen of niet?
En als zowel F2 als G2 niet is ingevuld, moet dan alleen het gemiddelde van MAX(H2:I2) en MAX(J2:K2) worden berekend?
Moeten ALLE 6 cellen ingevuld zijn om het gemiddelde van MAX(F2:G2); MAX(H2:I2); MAX(J2:K2) te berekenen?

[Arunia]

Ned 1.1	Ned 1.1 H	Ned 1.2	Ned 1.2 H	Ned 1.3	Ned 1.3 H	Gemiddeld
4,0	2,0	6,0	4,0	1,0	4,0	4,7

Ok, je hebt helemaal gelijk.
Door het hele verhaal er omheen, wordt het niet duidelijk.

Studenten hebben in dit voorbeeld 3 toetsen en 3 herkansingen.
Het gemiddelde wordt berekend over 3 toetsen, waarbij per set alleen de hoogste waarde gebruikt wordt.
Dus in bovenstaand voorbeeld alleen de waarde van Ned 1.1, 1.2 en 1.3 H. resp. een 4, 6 en weer een 4.

Daarnaast, wanneer er geen waarden ingevuld zijn, er een streepje in het "gemiddeld"-veld komt.
De 6 cellen moeten niet allemaal ingevuld te zijn om een gemiddelde te hebben.

En als zowel F2 als G2 niet is ingevuld, moet dan alleen het gemiddelde van MAX(H2:I2) en MAX(J2:K2) worden berekend?

Dat inderdaad.

Als alleen Ned 1.1 ingevuld is, dan is het gemiddelde een 4.
Zijn 1.1 en 1.2 ingevuld (dus niet de H van beide), dan is het gemiddelde een 5.
Is Ned 1.1 en 1.1 H ingevuld, dan moet daar de hoogste waarde ingevuld worden.

Ik hoop dat het er nu duidelijker op geworden is. (bij mij schieten mijn gedachten alle kanten op en heb nogal de neiging om het zo te omschrijven).

Bedankt voor het geduld!


Op de manier met de extra cellen van Solomon is het wel gelukt (gecombineerd met jouw ALS formule), maar het is nogal een gigantische lijst en kan me eigenlijk niet voorstellen dat het niet zonder die extra cellen zou kunnen.

[ Voor 7% gewijzigd door Arunia op 01-12-2014 15:28 ]

[naitsoezn]

[b]Arunia schreef op maandag 01 december 2014 @ 15:26:

Op de manier met de extra cellen van Solomon is het wel gelukt (gecombineerd met jouw ALS formule), maar het is nogal een gigantische lijst en kan me eigenlijk niet voorstellen dat het niet zonder die extra cellen zou kunnen.

Mjah, de vraag is toch waarom je niet zonder die extra cellen zou willen . Het is toch vaak een beetje de "Excel-manier" om veel hulpkolommen te gebruiken. Eventueel kun je die verbergen met "Hide column" als je anders het overzicht dreigt kwijt te raken

[Verwijderd]

Er zijn 8 mogelijkheden en om in alle situaties een correcte uitkomst te krijgen zullen die alle 8 berekend moeten worden, al dan niet met hulpkolom(men). In 1 formule ondergebracht geeft deze formule je altijd het juiste resultaat:

code:

=ALS(AANTAL(F2:K2)=0;"-";ALS(EN(AANTAL(F2:G2)>0;AANTAL(H2:K2)=0);MAX(F2:G2);ALS(EN(AANTAL(H2:I2)>0;AANTAL(F2:G2;J2:K2)=0);MAX(H2:I2);ALS(EN(AANTAL(F2:I2)=0;AANTAL(J2:K2)>0);MAX(J2:K2);ALS(EN(AANTAL(F2:G2)>0;AANTAL(H2:I2)>0;AANTAL(J2:K2)=0);GEMIDDELDE(MAX(F2:G2);MAX(H2:I2));ALS(EN(AANTAL(F2:G2)=0;AANTAL(H2:I2)>0;AANTAL(J2:K2)>0);GEMIDDELDE(MAX(H2:I2);MAX(J2:K2));ALS(EN(AANTAL(F2:G2)>0;AANTAL(J2:K2)>0;AANTAL(H2:I2)=0);GEMIDDELDE(MAX(F2:G2);MAX(J2:K2));GEMIDDELDE(MAX(F2:G2);MAX(H2:I2);MAX(J2:K2)))))))))

[Lustucru]

[b]Arunia schreef op maandag 01 december 2014 @ 15:26:ik kan me eigenlijk niet voorstellen dat het niet zonder die extra cellen zou kunnen.

Het probleem waar je tegenaanloopt is dat een functie in Excel altijd een resultaat oplevert, en dat gemiddelde() wel overweg kan met cellen die leeg zijn of tekst bevatten, maar niet met tekst in de argumenten zelf.

Het enige* wat je dan rest is de som van de max() te delen door het aantal geldige argumenten:

code:

=(MAX(A1:B1) + MAX(C1:D1) + MAX(E1:F1))
/
((AANTAL(A1:B1)>0) + (AANTAL(C1:D1)>0) + (AANTAL(E1:F1)>0))

Dit werkt omdat 'true' in excel gelijk is aan 1.

Dan nog afvangen als er helemaal niets is ingevuld en je bent er:

code:

=Als(aantal(A1:F1)>0;(MAX(A1:B1)+MAX(C1:D1)+MAX(E1:F1))/((AANTAL(A1:B1)>0)+(AANTAL(C1:D1)>0)+(AANTAL(E1:F1)>0));"-")

*Of alle mogelijkheden langslopen

[ Voor 3% gewijzigd door Lustucru op 01-12-2014 21:09 ]

[pedorus]

Voor de grap nog de variant (in de engelse excel) die hetzelfde doet maar makkelijk schaalbaar is tot vele toetsen:

code:

{=IFERROR(SUM(SUBTOTAL(4,OFFSET(A1:B1,0,(ROW(1:3)-1)*2))) /
          SUM(IF(SUBTOTAL(4,OFFSET(A1:B1,0,(ROW(1:3)-1)*2))>0,1,0)),"-")}

(array formula, ctrl-shift-enter om {} te krijgen)

Dus dat is zoiets in NL:

code:

{=ALS.FOUT(SOM(SUBTOTAAL(4;VERSCHUIVING(A1:B1;0;(RIJ(1:3)-1)*2))) /
           SOM(ALS(SUBTOTAAL(4;VERSCHUIVING(A1:B1;0;(RIJ(1:3)-1)*2))>0;1;0));"-")}

Als je dan 10 verschillende toetsen hebt, is het een kwestie van 1:10 neerzetten ipv 1:3.

Overigens zou ik zeggen dat het resultaat voor niet gedane toetsen gewoon een 0 is. Dus dan is het een stuk simpeler:

code:

{=AVERAGE(SUBTOTAL(4,OFFSET(A1:B1,0,(ROW(1:3)-1)*2)))}

Of eigenlijk wat al in de startpost stond. Dat voorkomt ook dat mensen stiekem maar naar 1 toets gaan

[Lustucru]

Subtotal icm offset. Mijn blinde vlek. Mooi gevonden.

[ Voor 10% gewijzigd door Lustucru op 01-12-2014 23:11 ]

[Arunia]

O
M
G

Ik heb die van pedorus geprobeerd, maar dan krijg ik standaard een streepje.
Dit ongeacht of ik wel of niet iets invul.

Ik heb de namelijk op G1 gezet om te testen.
A t/m F heb ik iets ingevuld.
Nu is het zo dat ik ook meteen de formule probeer te begrijpen.

Het probleem trouwens met toetsen welke een 0 als uitkomst bieden, is dat je dan een te laag cijfer krijgt als je tussentijds een cijferlijst uit draait. De docent geeft zelf aan dat ze moeten komen voor iig de eerste toetsen.

Die van Lustucru werkt trouwens wel. Is wellicht bewerkelijker (op sommige momenten), maar doet voor zover precies wat het moet doen.
Denk dat ik die ga gebruiken. De aanpassingen zijn voor mij net wat logischer (alhoewel ik die andere ook wel snap denk ik).

[ Voor 20% gewijzigd door Arunia op 02-12-2014 12:27 ]

[Lustucru]

pedorus schreef op maandag 01 december 2014 @ 22:20:
Voor de grap nog de variant (in de engelse excel) die hetzelfde doet maar makkelijk schaalbaar is tot vele toetsen:

Nog meer voor de grap: ik kom er nu pas achter dat je, als je een matrixconstante gebruikt in een formule, je kunt volstaan met een simpele enter.

De werkende Nederlandse variant, met ipv (rij(1:3))-1*2 de matrixconstante {0;2;4}

code:

=ALS.FOUT((SOM(SUBTOTAAL(4;VERSCHUIVING(A1:B1;0;{0;2;4}))))/(SOM(ALS(SUBTOTAAL(4;VERSCHUIVING(A1:B1;0;{0;2;4}))>0;1;0)));"-")

en gewoon bevestigen met enter


Als je dan 6 verschillende toetsen hebt, is het een kwestie van {0;2;4;6;8;10} ipv {{0;2;4}

[pedorus]

En dat kun je ook makkelijker ombouwen als je een extra herkansing hebt (als in VERSCHUIVING(A1;0;{0;2;5};1;{2;3;2}))) voor 2 herkansingen bij 2e toets).

Arunia schreef op dinsdag 02 december 2014 @ 12:14:
Ik heb die van pedorus geprobeerd, maar dan krijg ik standaard een streepje.
Dit ongeacht of ik wel of niet iets invul.

Waarschijnlijk was er nog een vertaalfoutje (heb net een , in ; veranderd), waardoor als.fout triggert, en je "-" ziet. Ik zou debuggen nooit met als.fout doen. Ook is ctrl-shift-enter belangrijk, of je moet gebruiken wat hierboven staat. Die zal vast wel getest zijn.

Maar ik zou vooral gaan voor iets dat je daadwerkelijk begrijpt, langer is dus niet zozeer slechter.

[Arunia]

Echt super bedankt allemaal voor alle moeite (ik weet dat het voor zich spreekt, maar toch).

Wat pedorus zegt, beter gaan voor iets wat ik begrijp en kan aanpassen, dan gaan voor iets wat ik minder begrijp en moeite heb met aanpassen. Nou denk ik dat het wel logisch is overigens, maar toch.

[Paultje3181]

En de functie isnb? Als(isnb(bla);"-";rest van je formule)