[Excel] uitkomst van een formule overnemen als waarde

[Goofyduck384]

Mijn vraag
Ik heb een excel bestand gemaakt waarin ik werk met 3 mogelijke notaties in een cel (NV, V of G). Deze drie mogelijkheden komen overeen met een bepaalde waarde (2, 6 of 10). Van 1-5 ingevulde cellen wordt dan het gemiddelde berekend, en deze wordt dan weer omgezet naar NV, V of G (echter nu met een andere verdeling (0-5,9=NV, 6-7,9=V en 8-10=G).

Nu zoek ik me suf hoe ik dat nieuwe uitgerekende gemiddelde nu in een ander tabblad kan overnemen als waarde (dus NV, V of G), zodat ik in dat tabblad weer a.d.h.v. de oorspronkelijke verdeling van 2, 6 of 10 pnt) wederom een gemiddelde kan uitrekenen.

Door gewoon in deze nieuwe cel een verwijzing naar de eerdere cel te plaatsen (='Bewegen verbeteren'!L4) komt de waarde (het uitgerekende gemiddelde) netjes in de cel te staan, echter kan ik daar niet goed mee door rekenen. Ik denk dat dit komt doordat niet alleen de waarde (NV, V of G) wordt overgenomen, maar ook het hele rekenproces daarachter incl de punten verdeling etc.

Hoe kan ik er nou voor zorgen dat deze waarde (die kan blijven wisselen, afhankelijk van nieuwe ingevoerde data) wordt overgenomen als losse letter, zodat ik daar weer mee verder kan rekenen?

Ik hoop dat mijn probleemstelling helder is, ik kom er echter niet uit..

[Sethro]

Kopieer en plakken als waarde? Anders via Macro fixen.

[Goofyduck384]

als ik hem plak als waarde, verandert hij niet mee wanneer de oorspronkelijke cel verandert wanneer er nieuwe (of extra) data wordt ingevoerd (waar dus ook een nieuw gemiddelde wordt uitgerekent)

[ Voor 3% gewijzigd door Goofyduck384 op 06-12-2017 22:30 ]

[Lustucru]

Met alle respect, maar je bent voor mij onbegrijpelijk. Je wilt een gemiddelde overnemen als waarde, maar die waarde mag geen waarde zijn, maar een berekend gemiddelde, en dat berekend gemiddelde mag geen getal zijn maar moet een letter zijn, en met die letter wil je kunnen rekenen.

Begin eens achteraan, want ik vermoed dat je een gemiddelde wilt afronden en van die afgeronde getallen een gemiddelde wilt hebben. Kan prima, maar dan moet eerst duidelijk zijn wat je wilt.

[Goofyduck384]

haha, ik was al bang dat het onduidelijk zou zijn.
Het gaat mij om het volgende:
- in Blad 1 cel D4 staat een letter (NV, V of G). Dit is geen losse waarde, maar een resultaat van een behoorlijke formule die prima werkt.
- In Blad 2 cel D4 moet deze waarde worden overgenomen voor een nieuwe reeks formules. Ik heb dit gedaan door de alom bekende manier =Blad!D4. Dan verandert de cel ook prima wanneer de bron-cel ook verandert.

Probleem is echter dat ik het idee heb dat niet alleen de losse waarde wordt meegenomen, maar eigenlijk de hele achterliggende bereking/formule. En dit mag niet (ivm de nieuwe formules die hierna volgen. Alleen de waarde mag worden overgenomen (dus de letters NV, V of G).

Is het zo iets duidelijker? Ik zou elke keer als er iets verandert de waardes kunnen kopieren en plalkken in de doel-cellen, maar dat is zo omslachtig. Er moet toch wel iets bestaan in Excel waardoor alleen telkens die waarde wordt overgenomen ipv ook de achterliggende formule?

[route99]

Als op Blad1 cel A1=2
en cel A2=2*A1
en dat is dan 4 als uitkomt in A2
Wil je op blad 2 het antwoord hebben zet je toch simple in die cel:
=Blad1!$A$2
Toch?

Hoe doe je dat sneller...
Copy Blad1 A2 en paste special in Blad2 cel A1
en kies dan in dat menu voor :
koppeling plakken....

Toch?

[ Voor 38% gewijzigd door route99 op 06-12-2017 23:03 ]

[White Feather]

Als je toch uit een formule een NV, V of G hebt gekregen, dan is het toch gewoon 1 van die 3 waarden? Er zit echt geen afgeronde NV(+1/4V) in de uitkomstcel of zo.

Van alle formules voordat je aan de NV, V of G komt, zijn teruggebracht naar 3 uitkomsten.

Hoezo heb je het idee dat niet alleen de losse waarde wordt meegenomen?

[bszz]

Goofyduck384 schreef op woensdag 6 december 2017 @ 22:52:
haha, ik was al bang dat het onduidelijk zou zijn.
Het gaat mij om het volgende:
- in Blad 1 cel D4 staat een letter (NV, V of G). Dit is geen losse waarde, maar een resultaat van een behoorlijke formule die prima werkt.
- In Blad 2 cel D4 moet deze waarde worden overgenomen voor een nieuwe reeks formules. Ik heb dit gedaan door de alom bekende manier =Blad!D4. Dan verandert de cel ook prima wanneer de bron-cel ook verandert.

Probleem is echter dat ik het idee heb dat niet alleen de losse waarde wordt meegenomen, maar eigenlijk de hele achterliggende bereking/formule. En dit mag niet (ivm de nieuwe formules die hierna volgen. Alleen de waarde mag worden overgenomen (dus de letters NV, V of G).

Is het zo iets duidelijker? Ik zou elke keer als er iets verandert de waardes kunnen kopieren en plalkken in de doel-cellen, maar dat is zo omslachtig. Er moet toch wel iets bestaan in Excel waardoor alleen telkens die waarde wordt overgenomen ipv ook de achterliggende formule?

Ha nee, het is niet duidelijker, althans niet voor mij. Wat is er zo verschillend aan het resultaat in de cel en het resultaat van de formule? Je uitleg doet vermoeden dat het format van de cel invloed heeft op hoe het resultaat van de formule weergegeven wordt. Kun je iets over de formule vertellen en iets over het format van de cel? Het duidelijkst is wellicht de formule zelf er bij te vermelden.

[Reptile209]

Goofyduck384 schreef op woensdag 6 december 2017 @ 22:52:
[..]
Probleem is echter dat ik het idee heb dat niet alleen de losse waarde wordt meegenomen, maar eigenlijk de hele achterliggende bereking/formule. En dit mag niet (ivm de nieuwe formules die hierna volgen. Alleen de waarde mag worden overgenomen (dus de letters NV, V of G).

[...]

Het maakt Excel niets uit of je met een vaste waarde doorrekent, of met de uitkomst van een berekening. Het enige verschil is natuurlijk dat een berekende waarde blijft meeveranderen als je dingen in je sheet aanpast.
Je zou alle formules die tot een resultaat leiden in één enkele cel kunnen plakken en Excel vindt het allemaal best. Hooguit dat er efficiënter gerekend wordt als dingen wat verspreid zijn (alleen de cellen die opnieuw berekend moeten worden zullen opnieuw berekend worden). In de praktijk zijn sheets maar zelden zo zwaar dat je dat verschil zult merken.

Kortom: ik zie geen enkele reden waarom je niet zou kunnen doorwerken met een resultaat van een formule. Ik doe dagelijks niet anders .

Kan je anders eens een minimaal maar concreet voorbeeld geven, en laten zien wat er anders is dan dat jij verwacht?

[Goofyduck384]

Oke, geen idee waar en hoe ik een excel bestand kan delen zodat het nog duidelijk kan worden 😀
Ik gebruik in een tabblad de volgende formule om uit een reeks van 4 beoordelingen het gemiddelde uit te rekenen:

=VERT.ZOEKEN(SOM(ALS.FOUT(VERT.ZOEKEN(L4;Rekenblad!$A$1:$B$3;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(M4;Rekenblad!$A$1:$B$3;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(N4;Rekenblad!$A$1:$B$3;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(O4;Rekenblad!$A$1:$B$3;2;ONWAAR);0);) / AANTALARG(L4:O4);Rekenblad!$D$1:$E$10;2;WAAR)

Dan ingevoerde reeks krijgt dan in een extra tabblad punten mee voor het uitrekenen van een gemiddelde. Dit gemiddelde wordt dan in die cel omgezet naar wederom een letter (maar dan via een andere indeling qua waarde).

En die letter moet dan overgenomen worden naar een ander tabblad om wederom deze formule te kunnen gebruiken voor weer een nieuw gemiddelde (met weer allerlei waarden uit diverse andere gemiddelden).

Alleen aangezien ik in deze eerste formule al twee verschillende punten aantallen gebruik, heb ik het idee dat het door een simpele verwijzing naar die cel (plak koppeling) het rekenen in de soep loopt. In ieder geval klopt het nieuwe gemiddelde niet meer. Daarom dacht ik dat misschien de formule ook meekomt ipv alleen het resultaat van die formule.

[Lustucru]

Goofyduck384 schreef op woensdag 6 december 2017 @ 22:52:
haha, ik was al bang dat het onduidelijk zou zijn.

Kom op, een docent moet beter kunnen.

Is het zo iets duidelijker?

Nee, want je herhaalt gewoon je startpost.

Er moet toch wel iets bestaan in Excel waardoor alleen telkens die waarde wordt overgenomen ipv ook de achterliggende formule?

ja, dat is standaard gedrag in Excel.

Leg nou eerst eens de berekening uit, en vooral, hoe je het gemiddelde uit wilt rekenen van "NV, V, G". Dat kan namelijk niet. Je zult eerst NV, V, en G in getallen om moeten zetten wil je daar een gemiddelde van kunnen berekenen.

Of laat zien wat het resultaat is en welk resultaat je had willen hebben en waarom. In elk geval klopt je analyse van het probleem voor geen meter.

[ Voor 9% gewijzigd door Lustucru op 06-12-2017 23:46 ]

[SinergyX]

Goofyduck384 schreef op woensdag 6 december 2017 @ 22:52:
Is het zo iets duidelijker? Ik zou elke keer als er iets verandert de waardes kunnen kopieren en plalkken in de doel-cellen, maar dat is zo omslachtig. Er moet toch wel iets bestaan in Excel waardoor alleen telkens die waarde wordt overgenomen ipv ook de achterliggende formule?

Dit snap ik nog steeds niet

Als ik in A1: (1+2+3+4+5+6+7+8+9+10)/5 heb staan en vervolgens zet in in B1: =A1, dan is B1 echt gewoon 11.

Als ik in C1: B1+1 doe, komt er gewoon 12 te staan, die hele formule in A1 doet hij helemaal niets mee.

De enige keer dat dit gebeurd als je celopmaak gebruikt bij bijvoorbeeld afronding, A1: 1,23456 met opmaak van 1 cijfer achter de komma (1,2) dan blijft =A1 gewoon diezelfde 1,23456.

Sidenote, maar vert.zoeken op basis van een som die bestaat uit een optelling van isfout(vert.zoeken), ik zou dat gewoon opknippen in 2 delen. Je kan gewoon data verbergen of een apart blad gebruiken voor data-cijfers, je hoeft niet alles in 1x in een cel als antwoord te hebben. De kans dat er een fout komt in die formule is groot en de formule wizard zal dat nooit netjes aangeven.

[ Voor 18% gewijzigd door SinergyX op 07-12-2017 00:10 ]

[Alex3]

Alleen de waarden 2 t/m 10 kunnen als gemiddelde voorkomen. De tabel in D1:E10 hoeft alleen de waarden 2, 6 en 8 te bevatten met daarbij NV, V en G als resultaat, en kan dus ingekort worden tot D1:E3. Je kunt de formule vereenvoudigen tot
=VERT.ZOEKEN(GEMIDDELDE(ALS.FOUT(VERT.ZOEKEN(L4:O4;Rekenblad!$A$1:$B$3;2;0);0));Rekenblad!$D$1:$E$3;2;1)
ingevoerd als matrixformule, d.w.z. met Ctrl+Shift+Enter.

[ Voor 15% gewijzigd door Alex3 op 08-12-2017 01:17 ]

[Z-Dragon]

Je bent bang dat er iets fout gaat, maar voor zover ik kan lezen, heb je nog geen voorbeeld dat dit werkelijk gebeurd is. Dat wekt bij ons de indruk dat je hard je best doet om een probleem te omzeilen dat niet bestaat en daar kunnen we je niet goed bij helpen. Alles heeft ervan weg dat je gewoon het standaardgedrag wilt, waar niets aan mankeert.

[ Voor 14% gewijzigd door Z-Dragon op 07-12-2017 03:11 ]

[Goofyduck384]

ik merk al dat het op deze manier inderdaad niet handig is. Man, wat zak ik door het ijs als docent (praatje-plaatje-daadje).
Dus misschien handiger om even het document hier te delen. Echter heb ik geen plek om dit online op te slaan en te delen. Waar kan ik mijn excel bestand uploaden en delen? Via google kom ik op de deel functie in excel zelf, echter heeft onze school één en ander aardig dicht getimmerd (voor de normale gebruikers zoals ik )
Denk dat als het document voor jullie beschikbaar is, het beter uit te leggen is wat ik wil en wat er nu fout gaat. Want er gaat iets fout (ik ben er niet bang voor, maar er klopt iets niet in de berekeningsstappen) en ik kan maar niet vinden wat het is.

[edit] Misschien zit het hier in (dat er iets ontbreekt in deze formule):

code:

=ALS.FOUT(VERT.ZOEKEN(SOM(ALS.FOUT(VERT.ZOEKEN(B4;Rekenblad!A1:B3;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(C4;Rekenblad!A1:B3;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(D4;Rekenblad!A1:B3;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(E4;Rekenblad!A1:B3;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(F4;Rekenblad!A1:B3;2;ONWAAR);0);) / AANTALARG(B4:F4);Rekenblad!$D$1:$E$10;2;WAAR);0)

Deze gebruik ik om uit 5 cellen het gemiddelde uit te rekenen. Stel dat er maar 4 van de 5 cellen zijn ingevuld, wordt dan toch het totaal door 5 (aantal cellen) gedeeld met deze formule? Want als dat het is, komt mijn probleem hier waarschijnlijk door. Kunnen jullie dat uit deze formule halen? En zo ja, hoe moet ik deze formule dan maken dat het gemiddelde wordt uitgerekend van de ingevulde cellen (en dus gedeeld door 4 cellen ipv 5).

[ Voor 38% gewijzigd door Goofyduck384 op 07-12-2017 13:53 ]

[breew]

@Goofyduck384 via imgur kun je vrij eenvoudig screenshots posten.. is beter dan niets...
Zorg je er wel voor dat er geen persoonsgegevens of andere privacygevoelige data gedeeld worden?

[breew]

Goofyduck384 schreef op donderdag 7 december 2017 @ 13:44:
[edit] Misschien zit het hier in (dat er iets ontbreekt in deze formule):

code:

=ALS.FOUT(VERT.ZOEKEN(SOM(ALS.FOUT(VERT.ZOEKEN(B4;Rekenblad!A1:B3;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(C4;Rekenblad!A1:B3;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(D4;Rekenblad!A1:B3;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(E4;Rekenblad!A1:B3;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(F4;Rekenblad!A1:B3;2;ONWAAR);0);) / AANTALARG(B4:F4);Rekenblad!$D$1:$E$10;2;WAAR);0)

Deze gebruik ik om uit 5 cellen het gemiddelde uit te rekenen. Stel dat er maar 4 van de 5 cellen zijn ingevuld, wordt dan toch het totaal door 5 (aantal cellen) gedeeld met deze formule? Want als dat het is, komt mijn probleem hier waarschijnlijk door.

ja, het lijkt er sterk op dat je ook lege cellen meeneemt in het gemiddelde.
Mogelijk. AANTALARG() telt ook soort-van-lege cellen (cellen met "" als waarde) en cellen met een foutwaarde mee. Alleen ècht lege cellen doen niet mee (maar het verschil tussen een ècht lege cel en een cel met "" erin, zie je met het blote oog niet)
Als je alleen cellen met cijfers wilt meenemen, moet je AANTAL() gebruiken.
bron

Als ik jou was, zou ik een hulpkolom aanmaken met daarin het aantal cellen waardoor je wilt delen. Dan kun je e.e.a. handmatig controleren.
je zou ook even kunnen kijken of ISLEEG() WAAR is voor B4:F4

[ Voor 29% gewijzigd door breew op 07-12-2017 14:53 ]

[Roozzz]

Goofyduck384 schreef op woensdag 6 december 2017 @ 22:52:
haha, ik was al bang dat het onduidelijk zou zijn.
Het gaat mij om het volgende:
- in Blad 1 cel D4 staat een letter (NV, V of G). Dit is geen losse waarde, maar een resultaat van een behoorlijke formule die prima werkt.
- In Blad 2 cel D4 moet deze waarde worden overgenomen voor een nieuwe reeks formules. Ik heb dit gedaan door de alom bekende manier =Blad!D4. Dan verandert de cel ook prima wanneer de bron-cel ook verandert.

Probleem is echter dat ik het idee heb dat niet alleen de losse waarde wordt meegenomen, maar eigenlijk de hele achterliggende bereking/formule. En dit mag niet (ivm de nieuwe formules die hierna volgen. Alleen de waarde mag worden overgenomen (dus de letters NV, V of G).

Is het zo iets duidelijker? Ik zou elke keer als er iets verandert de waardes kunnen kopieren en plalkken in de doel-cellen, maar dat is zo omslachtig. Er moet toch wel iets bestaan in Excel waardoor alleen telkens die waarde wordt overgenomen ipv ook de achterliggende formule?

Wat is nu eigenlijk het probleem? Heb je het idee dat er iets misgaat of gaat er iets mis? En wat dan?
Zoals jij wenst neemt Excel alleen de waarde over om mee verder te rekenen. Dus je probleem zal ergens anders liggen.

Om verder te rekenen met waardes als NV, V of G zal je ze eerst weer een getal moeten geven. Een V deelt nogal matig door een G.

[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?

[Reptile209]

Kijk, de aap is uit de mouw . Je gebruikt nu een formule om - bij een foutwaarde - een nul in te vullen. Zet daar in plaats van een 0 eens "". Dan is het resultaat een lege cel, die bij het berekenen van je gemiddelde wordt genegeerd

[breew]

Reptile209 schreef op donderdag 7 december 2017 @ 19:10:
Kijk, de aap is uit de mouw . Je gebruikt nu een formule om - bij een foutwaarde - een nul in te vullen. Zet daar in plaats van een 0 eens "". Dan is het resultaat een lege cel, die bij het berekenen van je gemiddelde wordt genegeerd

Dan moet je niet werken met AANTALARG, die neemt “” mee als cel met waarde.

[Goofyduck384]

Ik heb even geprobeerd om “” erin te zetten, maar inderdaad levert dit geen verschil op (behalve dat de cel idd leeg lijkt). Dus als ik die AANTALARG niet kan gebruiken, hoe moet ik die formule dan herschrijven? Ik begrijp helaas te weinig van al die formules vanuit mijzelf.

[ Voor 71% gewijzigd door Goofyduck384 op 07-12-2017 19:55 ]

[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]

Haha, dank voor dit gedrocht! Ik zal hem zo eens even proberen in mijn sheet. Excuses voor het fabriceren van deze omslachtige manier, ik was er bijna trots op Excel kan zoveel, maar je heb redelijk wat kennis nodig geloof ik..
Maar dat afronden in jouw formule, gebeurd dat altijd naar beneden? Of wordt bijv 7,5 afgerond naar een 8? Want alles onder de 8 moet een V worden. Dus als het gemiddelde een 7,9 is, moet dit vertaald worden naar een V.

[wibra]

sorry voor de off-topic, maar ik wist niet eens dat excel nederlandstalige formules had. dat is toch niet handig als je ergens een oplossing voor moet googlen, want dan moet je eerst het engelstalig equivalent opzoeken, en dan je gevonden oplossing terugvertalen naar het nederlands.


on-topic, over afronden, er zijn verschillende manieren hoe je kan afronden, maar ik ken enkel de engelse formules. er is ROUND(), CEILING() en FLOOr(). er zijn er nog, zoals mround en roundup, rounddown .

[ Voor 27% gewijzigd door wibra op 07-12-2017 20:24 ]

[Reptile209]

wibra schreef op donderdag 7 december 2017 @ 20:21:
sorry voor de off-topic, maar ik wist niet eens dat excel nederlandstalige formules had. dat is toch niet handig als je ergens een oplossing voor moet googlen, want dan moet je eerst het engelstalig equivalent opzoeken, en dan je gevonden oplossing terugvertalen naar het nederlands.
[...]

offtopic:
Er zijn zat conversie-lijstjes te vinden, zoals deze (willekeurig resultaat): http://www.piuha.fi/excel...e=nederlands-english.html

[breew]

Goofyduck384 schreef op donderdag 7 december 2017 @ 20:20:
Haha, dank voor dit gedrocht! Ik zal hem zo eens even proberen in mijn sheet. Excuses voor het fabriceren van deze omslachtige manier, ik was er bijna trots op Excel kan zoveel, maar je heb redelijk wat kennis nodig geloof ik..
Maar dat afronden in jouw formule, gebeurd dat altijd naar beneden? Of wordt bijv 7,5 afgerond naar een 8? Want alles onder de 8 moet een V worden. Dus als het gemiddelde een 7,9 is, moet dit vertaald worden naar een V.

AFRONDEN(getal;0) rond 0,5 en hoger naar omhoog af... Als dat niet is wat je wilt, moet je de verschillende functies/mogelijkheden voor afronden in excel even goed onder de loep nemen.
Het zijn er nogal wat
o.a. https://support.office.co...9d-4221-b21e-38da99d33435

Ik denk dat AFRONDEN.NAAR.BENEDEN() is wat je nodig hebt.. Test het eens op een paar decimale getallen, en kijk of het is wat je zoekt.
7,0 = 7
7,5 = 7
7,9 = 7

[ Voor 14% gewijzigd door breew op 07-12-2017 20:32 ]

[Lustucru]

breew schreef op donderdag 7 december 2017 @ 20:12:
@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

Tsja, want als iemand met een hamer een kruiskopschroef in een gipsplaat probeert te draaien reik je hem een moker aan?

Als ik het goed begrijp moet er in stap 1 een 'gemiddelde' berekend worden van een x-aantal waarden NV, V, en G, waarbij elke letter voor een bepaalde getalwaarde staat?

Wat is er dan mis met:

code:

=(aantal.als(A1:H1;"G")*10+aantal.als(A1:H1;"V")*6+aantal.als(A1:H1;"NV")*2)/aantalarg(A1:H1)

Dan pas een vert.zoeken eromheen voor de getalcodering en een als.fout om een codering aan te geven dat er nog geen enkele score bekend is. Afronden is ook niet nodig, door handig gebruik te maken van vert.zoeken met benaderen aan.

[ Voor 6% gewijzigd door Lustucru op 07-12-2017 20:34 ]

[breew]

Lustucru schreef op donderdag 7 december 2017 @ 20:32:
[...]
Tsja, want als iemand met een hamer een kruiskopschroef in een gipsplaat probeert te draaien reik je hem een moker aan?

Wat is er dan mis met:

code:

=(aantal.als(A1:H1;"G")*10+aantal.als(A1:H1;"V")*6+aantal.als(A1:H1;"NV")*2)/aantalarg(A1:H1)

Dan pas een vert.zoeken eromheen voor de getalcodering en een als.fout om een codering aan te geven dat er nog geen enkele score bekend is. Afronden is ook niet nodig, door handig gebruik te maken van vert.zoeken met benaderen aan.

Dat is inderdaad een veel elegantere oplossing!
Al denk ik dat je die AANTALARG moet vervangen, anders ga je ook de niet-zo-heel-erg-lege-cellen-die-wel-leeg-lijken meenemen in de deling. Dat was juist het probleem waar TS tegenaan liep.
Afronden gaat nog wel nodig zijn (in dit geval), aangezien 7,9 moet verwijzen naar de waarde bij 7, en niet naar 8.

[ Voor 10% gewijzigd door breew op 07-12-2017 21:04 ]

[Lustucru]

In het invulsheet komen vziz geen 'niet-zo-heel-erg-lege-cellen' voor? Maar anders iets als'delen door
aantal.als(A1:h1;">''")

breew schreef op donderdag 7 december 2017 @ 20:36:
Afronden gaat nog wel nodig zijn (in dit geval), aangezien 7,9 moet verwijzen naar de waarde bij 7, en niet naar 8.

Dat is toch precies hoe vert.zoeken() zich gedraagt?

breew schreef op donderdag 7 december 2017 @ 20:36:
Dat was juist het probleem waar TS tegenaan liep.

Je bent iig een stuk slimmer als ik, want ik had echt nog niet begrepen waar de TS tegenaan liep.

[ Voor 82% gewijzigd door Lustucru op 07-12-2017 21:18 ]

[Goofyduck384]

Lustucru schreef op donderdag 7 december 2017 @ 21:06:
Je bent iig een stuk slimmer als ik, want ik had echt nog niet begrepen waar de TS tegenaan liep.

Op dit moment loop ik vooral tegen mijn beperkte bovenkamer aan. Laat mij maar weer sport geven aan pubertjes....

Die door jullie genoemde elegante oplossing werkt inderdaad om het gemiddelde als een cijfer in de totaal kolom te krijgen. Nu moet ik die dus omzetten naar een letter. en zoals ik al zei gaan we nu redelijk de diepte in qua formules, dus kan iemand mijn huidige formule omzetten naar die elegante oplossing? In mijn huidige formule zien jullie wel waar ik de informatie vandaan haal (voor de letters en de waarde), maar ik weet niet zo goed hoe ik dit vertaal naar die nieuwe oplossing

code:

=ALS.FOUT(VERT.ZOEKEN(SOM(ALS.FOUT(VERT.ZOEKEN(H6;Rekenblad!$A$1:$B$3;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(I6;Rekenblad!$A$1:$B$3;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(J6;Rekenblad!$A$1:$B$3;2;ONWAAR);0);ALS.FOUT(VERT.ZOEKEN(K6;Rekenblad!$A$1:$B$3;2;ONWAAR);0);) / AANTALARG(H6:K6);Rekenblad!$D$1:$E$10;2;WAAR);0)

En hiermee reken ik dus het gemiddelde uit van de ingevulde waarden bij de tweede sheet "Bewegen verbeteren" toch? En dan in die eerste sheet "Totaal competenties" moet ik gewoon weer verwijzen naar de cellen die het totaal bevatten. En kan ik dan die formule van jullie ook daar gebruiken om van die nieuwe waarden wederom het gemiddelde te berekenen (zonder dat hij weer in de knoei komt met niet ingevulde cellen)?

[ Voor 13% gewijzigd door Goofyduck384 op 07-12-2017 21:21 ]

[Lustucru]

Didactisch verantwoord :

Opgave 1: reken het gemiddelde uit van G,G, V, en NV als G=10, V=6 en NV=2.
Hint: gebruik aantal.als en aantalarg

Opgave 2:
Typ in cel A1 het 5,7. Bepaal met een hulptabel een bijbehorende letter, als alles kleiner dan 6 een N moet worden, en alles vanaf 8 de letter G.

Controleer het resultaat met andere getallen die je in A1 kunt tikken.

Tip: gebruik vert.zoeken en het forum gathering of tweakers. Daar vind je vele voorbeelden van het gebruik van vert. zoeken

Opgave 3:
Combineer 1 en 2.

je hebt 15 minuten voor deze opdracht. Inleveren op GoT. Veel succes!

offtopic:
Voorzeggen verboden!

[ Voor 8% gewijzigd door Lustucru op 07-12-2017 21:30 ]

[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 ]

[breew]

@Goofyduck384 Je deelt door het aantal niet-lege cellen (AANTALARG), waardoor je ook de 0-en meeneemt.
Wat je wilt, is delen door het aantal cellen dat ongelijk is aan nul; dat aantal krijg je met AANTAL.ALS(), waarbij je als criterium gebruikt "<>0" (betekent: celinhoud is ongelijk aan 0)

[breew]

1. Ik zie trouwens dat je AFRONDEN.BENEDEN() gebruikt.. weet je zeker dat die functie doet wat je wilt (Rondt een getal naar beneden af, naar het dichtstbijzijnde meervoud van de significantie).
   Kijk even goed of AFRONDEN.NAAR.BENEDEN() niet is wat je wilt (Rondt een getal naar beneden af).
2. In je formule voor het VERT.ZOEKEN staat

   Rekenblad!D2:E11;2;ONWAAR

   Je zoekmatrix D2:E11 is dus relatief. Dat gaat mis als je de formule doortrekt naar een andere cel.
   Tenzij je een hele goede reden hebt om het relatief te laten, is het een best-practice om de zoekmatrix in VERT.ZOEKEN() altijd absoluut te maken (met $-tekens voor rij en kolom)
   Hoe/Wat: https://support.office.co...65-4f56-839e-5f0d8d0baca9

[ Voor 54% gewijzigd door breew op 08-12-2017 10:17 ]

[Lustucru]

Goofyduck384 schreef op vrijdag 8 december 2017 @ 09:24:
Dit is niet mooi, dus verander ik de code naar:
[mono]=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);"")[/code]

Bijna helemaal goed op drie dingen na:
- ik blijf van mening dat afronden niet nodig is als je vert.zoeken gebruikt met benaderen 'waar' of weggelaten. De opzoektabel kan dan ook simpeler met de waarden voor 0,6 en 8 (aka alleen de grenswaarden)

offtopic:
en wat breew ook opmerkt:

- als de letter het resultaat is van een formule gebruik je niet aantalarg() maar aantal.als()
- maak de zoekmatrix absoluut ($D$2:$E$11) ipv relatief.


Maar het blijft ondoorzichtig, dus wellicht beter voor je om je sheet simpel te houden en de functionaliteit te gieten in een eigen functie.

- druk op Alt+F11 om de visual basic editor te openen
- kies in de visual basic editor invoegen, module
- plak de onderstaande code in het module scherm;

Visual Basic:

Option Explicit

Public Function GEMIDDELDE_BEOORDELING(resultaten As Range) As String
    On Error GoTo nogo
    
    Dim teller As Long
    Dim noemer As Long
    Dim c As Range
    
    'loop door het resultatenbereik heen en zet letters om in een totaal
    'overige cellen worden genegeerd
    For Each c In resultaten
        Select Case UCase(c.Value)
        Case "G"
            teller = teller + 10
            noemer = noemer + 1
        Case "V"
            teller = teller + 6
            noemer = noemer + 1
        Case "NV"
            teller = teller + 2
            noemer = noemer + 1
        End Select
    Next c
        
    If noemer = 0 Then
        'als er geen resultaten zijn geef een '-' terug
        Gemiddelde_Beoordeling = "-"
    Else
        'zijn er wel resultaten,  bereken dan het gemiddelde en geef de corresponderende letter terug
        Select Case teller / noemer
        Case Is < 6
            Gemiddelde_Beoordeling = "NV"
        Case Is < 8
            Gemiddelde_Beoordeling = "V"
        Case Else
            Gemiddelde_Beoordeling = "G"
        End Select
    End If
    
    Exit Function
    
nogo:
    Gemiddelde_Beoordeling = Err.Description
End Function

- probeer de code stap voor stap te lezen zodat je snapt wat hij doet.
- sla je bestand als XLSM (spreadsheet met macro's)

Je hebt nu een nieuwe functie tot je beschikking: gemiddelde_beoordeling().
Die rekent voor je het gemiddelde uit van voorkomende letters N,V,G. Deze functie kun je gebruiken in je invulsheet en daarna ook weer op je totaalblad. Geen andere functies meer nodig.

[ Voor 16% gewijzigd door Lustucru op 08-12-2017 14:01 . Reden: front tegen underscores! ]

[breew]

@Lustucru zet je nog even een underscore middenin GemiddeldeBeoordeling, anders gaat 't mis ;-) (of geef de functie de naam GemiddeldeBeoordeling, dat kan natuurlijk ook )

@Goofyduck384 Is je rekenmethodiek wel eerlijk? Iemand met 4xV en 1xNV krijgt nu als eindscore een NV (4*6 + 1*2)/5 = 5.2 = NV

[ Voor 56% gewijzigd door breew op 08-12-2017 12:22 ]

[Goofyduck384]

Klopt, dan werkt hij inderdaad helemaal goed. Ik ga me eens goed verdiepen in de werking van deze laatste optie. Het laatste antwoord van @Lustucru is zeker het beste antwoord, echter de andere antwoorden hebben mij in ieder geval weer een hoop geleerd v.w.b. de diverse functiemogelijkheden in Excel. Bedankt daarvoor!

@breew je hebt helemaal gelijk. Ik probeer iets zo eerlijk mogelijk te maken, maar wel zo dat een NV (niet-voldoende) wel zwaar genoeg weegt om te verbeteren. Maar 4xV en 1xNV mag niet alsnog een NV opleveren nee. Daar moet ik nog eens goed naar kijken! Bedankt voor de tip.

[ Voor 30% gewijzigd door Goofyduck384 op 08-12-2017 12:57 ]

[Lustucru]

breew schreef op vrijdag 8 december 2017 @ 12:16:
@Lustucru zet je nog even een underscore middenin GemiddeldeBeoordeling, anders gaat 't mis ;-) (of geef de functie de naam GemiddeldeBeoordeling, dat kan natuurlijk ook )

Aaargh.

[Goofyduck384]

Ai, te vroeg gejuicht?!
Na het ingebruik nemen van mijn uiteindelijk resultaat blijkt dat Macro’s niet werken bij gebruik op de iPad... klein detail 😄.
Het document moet juist op de iPads gebruikt worden (in de gymzaal). Betekent dit dus terug naar af en toch lange ingewikkelde formules samenstellen?
Zo ja, welke formule moet ik nu dan gaan gebruiken? Ik had alles helemaal goed verwerkt. Op de pc werkt het nu top.

[breew]

Goofyduck384 schreef op woensdag 13 december 2017 @ 10:09:
Na het ingebruik nemen van mijn uiteindelijk resultaat blijkt dat Macro’s niet werken bij gebruik op de iPad... klein detail 😄.
Het document moet juist op de iPads gebruikt worden (in de gymzaal)

Klopt, geen vba op apple-spullen. Wat ik in dergelijke gevallen altijd doe, is het bestand draaien op een windows-pc, en daar (via bijvoorbeeld Teamviewer) op inloggen en het scherm overnemen. Dan kun je met alle devices (android, iOS, andere windows-machines, etc...) altijd in je bestand werken.

Heb je geen internetverbinding in de gymzaal? Dan zou je kunnen overwegen om een hotspot via een mobiele telefoon op te zetten.

[ Voor 20% gewijzigd door breew op 13-12-2017 10:14 ]

[Goofyduck384]

Jawel, we hebben zeker internet. Probleem is eerder dat ik iets moet verzinnen dat voor iedereen simpel werkbaar is, zonder dat er wat digitale kennis voor nodig is. Vandaar dat ik een kant en klaar excel document maak (beveiligt en verborgen op de juiste plekken) dat zij alleen maar hoeven in te vullen en dat automatisch alles doorberekent.
Gymdocenten zijn meestal niet te meest ICT vaardige mensen (ik reken Facebook niet tot een ICT vaardigheid), die liefst simpele middelen hebben. Dus geen teamviewer oplossingen, maar gewoon een simpel excel bestand (met hele lastige verborgen formules 😜)

[Lustucru]

Goofyduck384 schreef op woensdag 13 december 2017 @ 10:45:
(met hele lastige verborgen formules 😜)

Die kun je nog 'versimpelen' tot de volgende MATRIX-formule; in éé'n keer van letters naar letters:

code:

=ALS.FOUT(KIEZEN(GEMIDDELDE(ALS((B4:F4)="O";1;ALS((B4:F4)="G";5;ALS((B4:F4)="V";3;"-"))));"O";"O";"V";"G";"G");"-")

Invoeren met ctrl-shift-enter

[ Voor 10% gewijzigd door Lustucru op 16-12-2017 08:45 ]