Excel Berekening

[Verwijderd]

Hi all,

Ik wil graag een berekening doorvoeren dat wanneer ik een score toevoeg dat deze automatisch naar boven afrond, ook moet 0 naar boven worden afgerond naar 5 en moet alles boven de 21 21 zijn.

Voorbeeldje: als ik een score tussen 6-10 toevoeg, moet deze afgerond worden naar 10. Voeg ik een score van 21 of hoger toe, dan moet deze afgerond worden naar 21 en als ik een score 0 toevoeg moet de score ook 5 worden.

0=5
1 t/m 5 = 5
6 t/m 10 = 10
11 t/m 15 = 15
16 t/m 20 = 20
21+ = 21

Op dit moment heb ik het volgende kunnen verzinnen:

=ALS(ISLEEG(F6);"";ALS(EN(0<H6;21>H6);AFRONDEN(H6;5);ALS(H6=0;5;21)))

maar nu is 0 wel 5 en 21+ ook 21, alleen rond die de getallen van 1 t/m 20 niet af.

Misschien dat iemand mij kan helpen :)!

[Rannasha]

De functie AFRONDEN rondt komma-getallen af, die is niet bedoelt om gehele getallen te verhogen of te verlagen.

AFRONDEN(H6; 5) rondt een getal af op 5 cijfers achter de komma. Dus 3,1415926535 -> 3,14159. Niet wat je zoekt.

Je kunt hier omheen werken door het getal eerst door 5 te delen, dan af te ronden (op 0 cijfers achter de komma) en daarna weer met 5 te vermenigvuldigen.

Bijvoorbeeld: 12 -> 2.4 -> 2 -> 10
Of: 13 -> 2.6 -> 3 -> 15

Dit komt al meer in de buurt, maar je wil in jouw geval naar boven afronden. Hier is de functie AFRONDEN.BOVEN voor. Die rondt een komma-getal naar boven af. Dus 2.4 wordt 3.

Dus: Eerst delen door 5, dan AFRONDEN.BOVEN en daarna weer vermenigvuldigen met 5.

Het enige wat dan nog overblijft zijn de randvoorwaarden (0 en 21+). Die kun je blijven doen met de ALS() functie, maar je zou ook kunnen overwegen om gebruik te maken van MIN() en MAX(). Dit ziet er wellicht wat leesbaarder uit. De MIN() kiest de laagste van de inputs, MAX() de hoogste. De uitkomst moet altijd minstens 5 zijn, dus de uitdrukking MAX(5; <afrond code hier>) zorgt er voor.

Vervolgens weet je ook dat de hoogste waarde 21 is, dus als je de functie MIN(21; <max + afrond code hier>) gebruikt, komt dat ook meteen uit.

[heuveltje]

kun je dit niet net zo makelijk zonder afronden doen ?
als(f6>20;"21";(als f6>16;"20";(als f6>10;"15") etc..

[Angeloonie]

heuveltje schreef op donderdag 30 november 2017 @ 14:45:
kun je dit niet net zo makelijk zonder afronden doen ?
als(f6>20;"21";(als f6>16;20;(als f6>10;15) etc..

Was dit net aan het typen...

Dit staatje kun je regelrecht in een =ALS formule gieten:
0=5
1 t/m 5 = 5
6 t/m 10 = 10
11 t/m 15 = 15
16 t/m 20 = 20
21+ = 21

[Unusable]

Hoi Floydk,

Misschien kan je zelfs iets met modulo.

16,4 -> =mod(16,4;5) = 1,4
16,4 - 1,4 +5 = 20

7,8 -> =mod(7,8;5) = 2,8
7,8 - 2,8 + 5 = 10

Oftewel in excel:

A1 = 3,4
B1 =IF(A1>=21;21;A1-MOD(A1;5)+5) = 5

A2 = 8,1
B2 =IF(A2>=21;21;A2-MOD(A2;5)+5) = 10

A3 = 21.6
B2 =IF(A3>=21;21;A3-MOD(A3;5)+5) = 21

[ Voor 37% gewijzigd door Unusable op 30-11-2017 15:17 ]

[Verwijderd]

Allen bedankt voor het snelle reageren.

Ik miste dus het woordje boven... Ik hoef namelijk niet meer te delen en kom met .boven op het juiste uit.

Als ik die van jullie (Heuveltje & Angeloonie) krijg ik een foutmelding dat ik teveel argumenten heb gebruikt? Deze ziet er wel stukken fijner uit.

Unusable, dit is mij denk ik iets teveel van het goede. Zelf nog nooit met mod gewerkt, maar snap hier ook helemaal niets van haha ;p

In ieder geval allemaal bedankt voor de snelle hulp, met sommige (simpele) formules kom ik nog wel uit de voeten maar sommige zijn echt abracadabra voor mij, moet altijd iets te leren hebben toch!

[breew]

Nog niet gemeld: Als je Excel 2016 hebt, kun je gebruik maken van ALS.VOORWAARDEN() (Engels: IFS): https://support.office.co...b2-467c-972b-4a39bd951d45

Hieronder een overzicht van de eerder aangedragen oplossingen.
Mijn persoonlijke voorkeur gaat uit naar de oplossing met AFRONDEN.BOVEN(), want dat is beter schaalbaar en, imho, leesbaar. Als je door wilt tot 1001, hoef je alleen de 21 te vervangen.

=ALS(A1=0;5;ALS(A1>=21;21;AFRONDEN.BOVEN(A1/5;1)*5))

De oplossing met de geneste ALS() vind ik slechter leesbaar.. En stel je eens voor dat je categoriën wilt t/m 1000. (dat kan trouwens niet, want je mag (dacht ik) maximaal 64 geneste ALS()-statements hebben in Excel).

=ALS(A1<6;5;ALS(A1<11;10;ALS(A1<16;15;ALS(A1<21;20;21))))

Je kunt de volgorde uiteraard ook omdraaien, en aftellen van 21 naar 0. Dan werkt je met > ipv <.

Lange geneste ALS/EN/OF-functies zijn een drama.. Je moet ze 100% correct invoeren, anders kunnen ze in (bijvoorbeeld) 80% van de gevallen werken, en in 20% van de gevallen verkeerde antwoorden genereren. (zonder dat je het wellicht doorhebt). Succes met debuggen dan . Andere functies vallen vaak veel sneller door de mand als ze fouten bevatten.
ook M$ zegt het :)

[ Voor 35% gewijzigd door breew op 30-11-2017 18:39 ]

[Arjan90]

Is het niet veel makkelijker? Als de waarde 21 of hoger is, is het 21. Voor alle andere waarden geldt dat je het moet delen door 5, afronden naar boven en dan moet vermenigvuldigen met 5.

Edit: zelfde oplossing als Breew Sorry!

[ Voor 10% gewijzigd door Arjan90 op 30-11-2017 18:45 ]

[breew]

nevermind

[ Voor 94% gewijzigd door breew op 30-11-2017 18:50 ]

[Freee!!]

Arjan90 schreef op donderdag 30 november 2017 @ 18:45:
Is het niet veel makkelijker? Als de waarde 21 of hoger is, is het 21. Voor alle andere waarden geldt dat je het moet delen door 5, afronden naar boven en dan moet vermenigvuldigen met 5.

Edit: zelfde oplossing als Breew Sorry!

Werkt niet als de waarde 0 is.

[Lustucru]

breew schreef op donderdag 30 november 2017 @ 18:31:
Mijn persoonlijke voorkeur gaat uit naar de oplossing met AFRONDEN.BOVEN(), want dat is beter schaalbaar en, imho, leesbaar. Als je door wilt tot 1001, hoef je alleen de 21 te vervangen.

=ALS(A1=0;5;ALS(A1>=21;21;AFRONDEN.BOVEN(A1/5;1)*5))

Sowieso heb ik een bloedhekel aan ALS() als het gaat om leesbaarheid. Je wilt een uitkomst beperken tussen minimale en maximale waarden? Gebruik dan ook min en max. Je wilt afronden op een veelvoud? Gebruik dan afronden naar veelvoud. Enige probleem is dan nog dat je niet wilt afronden naar het dichtstbijzijnde veelvoud maar naar het veelvoud wat hoger ligt. Dat bereik je door er N/2 bij op te tellen.

code:

=MAX(5;MIN(21;AFRONDEN.N.VEELVOUD(A1+2;5)))
'of als je toch liever afronden naar boven gebruikt
=MAX(5;MIN(21;AFRONDEN.BOVEN(A1/5;1)*5))

[ Voor 5% gewijzigd door Lustucru op 30-11-2017 20:13 ]

[Verwijderd]

Vele wegen leiden naar Rome zo te zien .

Allen bedankt voor het meedenken! Zitten een paar leuke 'wegen' tussen.