Excel: 2 kolommen delen met voorwaarden

[mgrevenstuk]

Hallo, Ik heb een vraag die voor een ge-oefende excel gebruiker wellicht niet eens zo moeilijk is. Maar ik kom er niet uit. Het volgende: ik gebruik een som.als formule om een bereik in kolom C te laten optellen als er in kolom B aan een bepaalde voorwaarden wordt voldaan.

Bijvoorbeeld: in kolom A een aantal namen van mensen, in kolom B de waarde 'Man' of Vrouw''en in kolom C het aantal kilometers wat zij in een bepaalde week hebben gelopen. Ik wil alleen de kilometers opgeteld hebben van de 'vrouwen' (in kolom B wordt meerdere keren 'vrouw' genoemd).

Bovenstaande lukt nog wel.

Nu wil ik iets ingewikkelders:

Namelijk nog steeds een vergelijkbare SOM.ALS structuur: dus bijvoorbeeld nog steeds het aantal kilometers (uit kolom C) van alleen de vrouwen (uit kolom B). Maar nu moet het aantal kilometers gedeeld worden door de waarde in kolom D, het aantal keren dat zij die week hebben gelopen. Zie hieronder:

=SOM.ALS(B2:B14;A18;(C2:C14)/(D214))

Smiley in bovenstaande formuler is gewoon een dubbele punt, maar krijg het niet weg...

Maar dat lukt dus niet. Zou super zijn als iemand mij kan helpen.

 

[snb]

Kijk eens naar wat draaaitabellen vermogen.

 

[VenA]

Een voorbeeldbestandje wil ook nog wel eens helpen om direct een passend antwoord te krijgen. : D zonder de spatie ertussen geeft inderdaad een

Dit kan je voorkomen door gebruik te maken van de code- of phptags.

=SOM.ALS(B2:B14;A18;(C2:C14)/(D2:D14))

=SOM.ALS(B2:B14;A18;(C2:C14)/(D2:D14))

 

[mgrevenstuk]

De smiley is het probleem niet, die kwam alleen voor in de notitie die ik maakte op dit forum. Het gaat om de formule zelf, die werkt niet in excel.
Ik heb nu een bestandje toegevoegd met een voorbeeld case.

Mvrgr, Michel

Bekijk bijlage test.xls

 

[MarcelBeug]

En dan heb je nog niet vermeld dat er ook cellen zijn met "Man / vrouw" erin...

Ik kom op in D21 en gekopieerd naar rechts en naar beneden tot en met J22:

=SOMPRODUCT(ISGETAL(VIND.SPEC($B21;$B$3:$B$18))*D$3:D$18)/SOMPRODUCT(ISGETAL(VIND.SPEC($B21;$B$3:$B$18))*$C$3:$C$18)

Maar het kan best zijn dat ik de vraag toch niet goed begrepen heb.

 

[Excelbat]

Probeer deze eens in cel D21:

=SOMPRODUCT(($B$3:$B$19=$B21)*($C$3:$C$19)*(D$3:D$19))

Greetz/Excelbat

 

[Excelbat]

In de oplossing van MarcelBeug #5 worden de kilometers van de combinatie "Vrouw, man" dus dubbel geteld, en dat kan m.i. niet juist zijn.
In mijn oplossing wordt de combinatie in het geheel niet meegenomen. Lijkt me dat TS hierin duidelijkheid moet verschaffen.
Oplossing zou zijn om in B23 een aparte regel te maken voor de combinatie "Vrouw, man".

Greetz/Excelbat

 

[mgrevenstuk]

Een dubbeltelling is zeker niet wenselijk. Een dit soort cases komen voor in het grotere excel-overzicht waarvoor ik het nodig heb. Maar de opzet van Marcel vind ik wel heel interessant en die richting had ik nog niet op gedacht. Ik zat vanmiddag op de route van matrixformules, maar daar kwam ik niet helemaa uit, want dat is ook nieuw voor mij.

@Marcel: super bedankt voor het meedenken. Ik ga er vanavond even gedetailleerd naar kijken en kom er bij je op terug. Als er inderdaad een dubbeltelling in zit, dan is dat nog wel iets waarvoor ik een oplossing moet bedenken.

Groet, Michel

 

[mgrevenstuk]

@Excel/Bat: aparte regel begrijp ik in dit voorbeeld. In de werkelijke toepassing heb ik meerdere combinaties van namen, waaruit de juiste naam moet worden gevonen. Dus bijv: Michel in een veld met alleen Michel of in een veld met Karin, Pietje, Klaasje, Michel...

Dank ook voor het meedenken. De reacties op dit Forum zijn snel en goed; dat stemt mij positief in het vinden van de oplossing ;-)

 

[snb]

Ik was niet van plan formules te bedenken voor verkeerd gestructureerde data.

zorg dat in kolom B, zoals het hoort in een database, altijd maar 1 gegeven komt te staan.

Dan volstaat in D12:

=SUMPRODUCT((B3:B18=B21)*(D3:D18))/SUMPRODUCT((B3:B18=B21)*(C3:C18))

Maar wenseljker zou zijn dat per deelnemer per tocht het aantal gelopen km. wordt vermeld.
Dan kan kolom C vervallen en komt in kolom D alleen het aantal gelopen kilometers per tocht te staan.
In kolom E komt dan de datum te staan zodat we later kunnen aggregeren op dag/week/maand/periode.

 

[mgrevenstuk]

Oplossing...denk ik

Ik denk er uit te zijn:

Het ging mij er om dat ik van bijv. de "vrouwen" het totaal van de gemiddelde km per wandeling / per week zou krijgen. Met enige ombuiging van de formule van Marcel en aandachtspunten van Excelbat, ben ik eruit gekomen. zie bijlage. Lijkt geen dubbeltelling in te zitten.
Hartelijk dank voor het meedenken!!
Mvrgr, MichelBekijk bijlage Wandelende vrouwen en mannen MB_MG.xls

 

[mgrevenstuk]

Dank Excelbat en MarcelBeug

Ik denk dat het nu allemaal loopt zoals het moet... Zie geen foute totalen. Daarmee hartelijk dank voor jullie input. Ik noteer jullie usernamen wel even. Als er nog iets is dan klop ik graag aan. Dank voor de genomen moeite.

Groet, Michel