[Excel] Dynamisch een aantal cellen sommeren

[Ravhin]

Het probleem waar ik mee zit is het volgende:

Ik heb een lijst met getallen waar over het jaar meer getallen bij komen:

code:

 A  B  C  D  E
100 30 75 60 80

Vervolgens rangschik ik de getallen naar grootte d.m.v. RANG(A1;E1,1) waarbij je het laatste getal laat oplopen:

code:

 A  B  C  D  E [...] K  L  M  N  O
100 30 75 60 80[...]100 80 75 60 30

Nu wil ik voor elkaar krijgen dat het gemiddelde genomen wordt van
de helft van deze getallen + 1! Dat helft plus 1 laten uitrekenen kan ik laten doen door INTEGER((AANTAL(K1;O1))/2+1), maar kan ik in GEMIDDELDE() nu voor elkaar krijgen dat het bereik dynamisch genomen wordt? Als ik er weer meer getallen bij ga zetten moet het aantal weer opnieuw berekend worden en dan moeten dus ook deze getallen die d.m.v. RANG op een rijtje zijn gezet meegenomen worden in de berekening voor het gemiddelde?

Ik vrees dat ik hiervoor Visual in moet duiken, maar is er geen andere mogelijkheid?

[wouter12345]

ik snap niet precies wat je wil doen,
maar zou je het gemiddelde dan niet als volgt kunnen berekenen?

in bijvoorbeeld cell A3 zet je de formule
=INTEGER((AANTAL(K2;O2))/2+1)
en dan in cell A4
=(SUM(K1:O1))/$A$3

[Ravhin]

Het probleem waar je mee zit is dat RANG() een foutmelding geeft op het moment dat er op de plaats van verwijzing geen getal aanwezig is. Dat kan je niet in je bereik van je GEMIDDELDE() hebben want dan geeft die ook een foutmelding...
Die RANG() reeks heb ik er al (statisch) inzitten. Omdat ik niet weet hoeveel van deze getallen gegenereerd worden (12-20 stuks) heb ik gewoon een reeks van 20 RANG() gemaakt waaruit dus de helft+1 genomen moet worden. Je krijgt dus bij (bijna) elke invoering van een extra getal een herberekening van [de helft +1] en dan moet hij het gemiddelde daarvan nemen ---> en die verwijzing wordt dus dynamisch:
GEMIDDELDE(K1;[K+[de helft+1]]1)

Snappie?

[ Voor 6% gewijzigd door Ravhin op 22-12-2004 11:21 ]

[wouter12345]

ik denk dat zoiets idd alleen in Visual Basic gaat

alleen dan nog een vraag: je hebt nu 5 getallen onder
K1 L1 M1 N1 O1
De helft hiervan + 1 is gelijk aan 3,5.

en dan wil je het gemiddelde van K1 tot 3,5 cellen naar rechts, dus
K1 L1 M1 en de helft van N1???

ik zal het nog wel niet helemaal goed begrijpen, want die kan natuurlijk niet.

[Woudloper]

Kan je dit niet gewoon doen met SUMIF? Je kan bij SUMIF namelijk een range, criteria en sum_range opgeven.

Als ik jou was zou ik dan gewoon en rij 1 de gegevens laten staan daaronder (in rij 2) de RANK/RANG functie tonen. Vervolgens tel je het aantal gevulde waardes en deel je door 2.

Bij de SUMIF doe je een optelling voor de range waarin de rang/rank staat en je telt vervolgens de waarden op.

[Ravhin]

Woudloper schreef op woensdag 22 december 2004 @ 11:44:
Kan je dit niet gewoon doen met SUMIF? Je kan bij SUMIF namelijk een range, criteria en sum_range opgeven.

Ehm, ja, kan je dan je sum_range dynamisch maken of hoeft dat niet??

Als ik jou was zou ik dan gewoon en rij 1 de gegevens laten staan daaronder (in rij 2) de RANK/RANG functie tonen. Vervolgens tel je het aantal gevulde waardes en deel je door 2.

Bij de SUMIF doe je een optelling voor de range waarin de rang/rank staat en je telt vervolgens de waarden op.

Ik snap niet helemaal hoe dat tot een bevredigend resultaat kan komen? Kan je een voorbeeld geven?

Ik heb gekeken bij SUMIF maar dat geeft alleen maar aan dat hij sommeert wanneer er aan een bepaalde voorwaarde wordt voldaan.
* Ravhin ziet een oneindige reeks geneste IF's die waarschijnlijk niet in het celletje gaan passen...

[Ravhin]

wouter12345 schreef op woensdag 22 december 2004 @ 11:43:
[...] en dan wil je het gemiddelde van K1 tot 3,5 cellen naar rechts, dus
K1 L1 M1 en de helft van N1???

ik zal het nog wel niet helemaal goed begrijpen, want die kan natuurlijk niet.

Ik neem daarom de integer van deze functie, en die rond automatisch af op een geheel getal en naar beneden. Op die manier kom je altijd uit op een geheel getal

[Woudloper]

Ik dacht aan het volgende:

	A	B	C	D	E	F	G	H	I	J
1	6	4	7	9	2	10	4	7	3	1
2	30	23	45	67	10	100	23	45	12	5

In rij 1 staat de rank functie welke voor A1 als volgt is:

code:

=RANK(A2;$A$2:$J$2;1)

Vervolgens plaats ik dan in een cell waar de optelling van de eerste helft gedaan moet worden de volgende code:

code:

=SUMIF(A1:J1;"<5";A2:J2)

In dat geval telt hij namelijk alleen de waardes op die onder de helft vallen. Verder kan je de criteria dan nog dynamisch maken en moet het toch ook werken? Of zit ik nu verkeerd?

[Ravhin]

Tissim!

Het bekende om-het-bochtje-denken!

Als ik het criterium dynamisch maak (en dat kon idd door bv celverwijzing) dan ben ik er! Mijn dank!
-------update-------

Ik heb dit even uitgeprobeerd maar helaas gaat dit niet lukken. In het criterium kan je niet aangeven dat als voorwaarde de rank kleiner of gelijk moet zijn dan een cel waar [de helft +1] wordt uitgerekend. Iemand nog meer slimme ideeën?

[ Voor 46% gewijzigd door Ravhin op 22-12-2004 13:22 ]

[onkl]

Denk dant je vastloopt op "<5" dynamisch maken.
Probeer eens
1: bereken het aantal cellen waarin wat staat:
countif(A1:Z1)
de integerhelft hiervan (prachtwoord niet?)
integer(countif(A1:Z1)/2)
er een tekst van maken:
concatenate("<";integer(countif(A1:Z1)/2))
en daarmee de ""<5" vervangen.
=SUMIF(A1:J1;concatenate("<";integer(countif(A1:Z1)/2));A2:J2)
of, een langere omweg:
als in cel A3 je [helft +1] is:
=SUMIF(A1:J1;concatenate("<";A3);A2:J2)
je moet iig. een string, (tekst) hebben bij criterium in je countif.

[Ravhin]

Top!

Ik ben er uit:

code:

=(SUMIF(K1:O1;CONCATENATE("<";Z1);A1:E1))/(Z1-1)

waarbij Z1:

code:

=INT((COUNT(K1:O1)/2)+1)+1

De extra +1 om te compenseren voor de "<" i.p.v. "<="

Mijn dank is groot!