[Excel] uitkomst van een formule overnemen als waarde

[Goofyduck384]

Hier drie plaatjes van mijn sheets.


De eerste is het totaal blad waar de resultaten (totaal kolom uit de tweede sheet) automatisch in overgenomen. Deze cellen verwijzen dus middels ='Bewegen verbeteren'!F4.
Echter inderdaad zijn de lege cellen in de tweede sheet niet ‘leeg’, maar bevatten 0 als er een foutcode ontstaat (doordat er nog soms geen gegevens zijn ingevuld, delen door 0 kan namelijk niet). De 0 heb ik met voorwaardelijke opmaak onzichtbaar gemaakt, waardoor de cellen leeg lijken. Hier zal de fout dus in zitten dat ik in de eerste totaal sheet dus geen gemiddelden kan uitrekenen (of in ieder geval kloppen die gemiddelden niet, zoals je bij leerling 1 ziet. Een G en een V kunnen nooit een gemiddelde NV geven (berekening qua punten staat in de laatste sheet, het Rekenblad

Ik denk dus dat het komt door het hierboven genoemde feit dat de cellen niet echt leeg zijn. Hoe los ik dit dan op?

[breew]

@Goofyduck384 ,
Ik heb geprobeerd me te houden aan de logica die in jouw sheets zit.. Dat stuit me een klein beetje tegen de borst, want het is de meest omslachtige manier die ik ooit heb gezien om een gemiddelde te bepalen

Buckle up, here we go

Schrik niet, want een lelijker formule heb ik nog nooit gemaakt in excel....

Dit is een screenshot van mijn testsheet (ik heb de nullen in C2, D2 en F2 even zichtbaar gemaakt):


In G2 staat het volgende gedrocht:

=VERT.ZOEKEN(AFRONDEN(SOM(ALS.FOUT(VERT.ZOEKEN(B2;$A$7:$B$9;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(C2;$A$7:$B$9;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(D2;$A$7:$B$9;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(E2;$A$7:$B$9;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(F2;$A$7:$B$9;2;ONWAAR);0))/AANTAL.ALS(B2:F2;"<>0");0);$F$7:$G$16;2;ONWAAR)

wat doet deze formule:

1. haal voor B2 t/m F2 middels VERT.ZOEKEN() de waarde op uit de tweede kolom van de matrix A7:B9
2. kan de waarde niet gevonden worden ALS.FOUT() , neem dan als waarde '0'
3. sommeer SOM() deze vijf waarden (10 + 0 + 0 + 6 + 0 = 16)
4. deel deze door het aantal waarden ongelijk aan 0; AANTAL.ALS(B2:F2;"<>0") (in dit geval 2)
5. voor de zekerheid (en de volgende stap): rond de uitkomst van de deling af naar 0 decimalen; AFRONDEN(uitkomst;0)
6. zoek dit (altijd hele) getal met VERT.ZOEKEN() in de matrix F7:G16, neem als uitkomst de waarde uit de tweede kolom.

Dit gezegd hebbende:
Ik raad je aan om de boel grondig om te bouwen en een eenvoudiger, beter beheerbare, begrijpelijke manier te kiezen om een gemiddeld eindcijfer te bepalen
Stel je voor dat je over twee jaar je sheet aan moet passen.. snap je deze formulebrij dan nog steeds?

[ Voor 8% gewijzigd door breew op 07-12-2017 20:26 ]

[Goofyduck384]

Gelukkig is tijd een relatief begrip, dus hopelijk ben ik nog net binnen de tijd.
Ik heb deze gecombineerde formule gemaakt:

code:

=VERT.ZOEKEN(AFRONDEN.BENEDEN((AANTAL.ALS(H5:K5;"G")*10+AANTAL.ALS(H5:K5;"V")*6+AANTAL.ALS(H5:K5;"NV")*2)/AANTALARG(H5:K5);WAAR);Rekenblad!D2:E11;2;ONWAAR)

Zo reken ik in ieder geval het gemiddelde uit van de beschikbare data en zet ik hem netjes om naar een letter. Alleen als er dus nog geen data bekend is (begin van het schooljaar) krijg ik een foutmelding in de totaal kolom. Dit is niet mooi, dus verander ik de code naar:

code:

=ALS.FOUT(VERT.ZOEKEN(AFRONDEN.BENEDEN((AANTAL.ALS(H5:K5;"G")*10+AANTAL.ALS(H5:K5;"V")*6+AANTAL.ALS(H5:K5;"NV")*2)/AANTALARG(H5:K5);WAAR);Rekenblad!D2:E11;2;ONWAAR);"")

Echter als ik dan weer in het totaal tabblad kijk, neemt hij dus de 'lege cellen' wel mee in het berekenen van het gemiddelde (terwijl die cel dus nog leeg is).


Hier zie je bij leerling 3 dat het gemiddelde van 2 ingevoerde waarden (in de invulsheets) 1,6 is (2+6+0+0+0 / 5 = 1,6)..

Dus de oplossing is nu wel eleganter in de invulsheets, deze begrijp ik ook. Maar nu blijft het probleem nog bestaan in de Totaal sheet, waar ik de gemiddelden van de diverse onderdelen ook weer middel en omzet naar een letter.

[ Voor 7% gewijzigd door Goofyduck384 op 08-12-2017 09:44 ]