gemiddelde uitrekenen met variabele gegevens

[ro2b]

In bijgaand excel wil ik in sheet PLOEGEN het gemiddelde uit laten rekenen van een variabel aantal deelnemers per ploeg en variabel per dag.

Dus de deelnemers van de ploegen staan (mogelijk) vast, ben nog aan het denken om het aantal per ploeg per dag variabel te maken.
Nu moet er per etappedag (variabel via INDIRECT) een gemiddelde uitgerekend worden van de per etappedag gereden tijden. Ik heb een poging gewaagd met VERT.ZOEKEN en INDIRECT maar enerzijds wordt het te ingewikkeld en anderzijds werkt het in kolom B niet (#N/B).

BESTAND: Bekijk bijlage gemiddelde van variabele gegevens.xlsm

Wie heeft een snellere en werkende oplossing?

groet,
RO2B

 

[HSV]

Ik heb niet gekeken voor een ander formule.
Een heleboel Excellers meent te weten hoe de functie 'Vert.Zoeken' werkt.
Hier weer zo'n voorbeeld zonder het argument 'benaderen' in te vullen.
Druk op de knop ƒx links van de formulebalk, en loop met de cursor door de formule totdat je alle 'benaderen' argumenten hebt gevuld met een 0 (nul).
Nu heb je in ieder geval al een uitkomst in B14.

 

[ro2b]

Een heleboel Excellers meent te weten hoe de functie 'Vert.Zoeken' werkt.

Ik beweer zeker niet dat ik weet hoe deze functie werkt, niet anders dan het verticaal zoeken van een waarde in een matrix en dan de bijbehorende waarde terugkrijgen).

Verder zie ik niet wat je bedoel. De fx heb ik gedaan maar zie nergens een 'benaderen' argument die ik op NUL kan zetten.

 

[HSV]

Ga eens met de cursor op de functie 'Vert.Zoeken' staan.

 

[Timshel]

Harry heeft wel gelijk met zijn beschrijving van de VERT.ZOEKEN functie. Die functie heeft 4 argumenten waarvan jij er telkens maar 3 gebruikt. Het 4e argument (benaderen) moet in dit geval overal 0 zijn. Zie de excel-help voor deze functie.
Overigens ben ik het met je eens dat wat jij wil te ingewikkeld is voor een geneste VERT.ZOEKEN-functie. Ik zou het zo doen:

=SOMPRODUCT(ISGETAL(VERGELIJKEN(INDIRECT("'dag"&$B$1&"'!$C$8:$C$19");B$3:B$12;0))*INDIRECT("'dag"&$B$1&"'!$E$8:$E$19"))/AANTAL.ARG(B$3:B$12)

 

[plongske]

totdat je alle 'benaderen' argumenten hebt gevuld met een 0 (nul)

of "onwaar" hebt ingevuld

 

[Timshel]

Overigens werkt je TIJDWAARDE-functie ook nog niet helemaal lekker.
Hij moet zo in E8:

=TIJDWAARDE(ALS(ISTEKST(A8);TEKST($D$2/SUBSTITUEREN(A8;"k";"")/24;"u:mm:ss");TEKST(A8;"00\:00\:00")))

... zodat het er niet alleen uitziet als tijd maar je er ook nog mee kunt rekenen.

 

[ro2b]

Hoi Timshel,

Ik krijg nu een foutmelding #naam?

Verder begrijp ik niet wat nu gevraagd wordt mbt nul waarde. Ik kopieer de functie van die het wel doet en het werkt nog steeds niet. Maar als jouw formule het doet maakt het ook niet meer uit ;-)

 

[Ginger]

@Timshel, gevalletje helaas pindakaas. Mijn formule spuugt zowel kilometers als tijd uit. Dus de plek waar je nu de functie "tijdwaarde" hebt geplaatst, is niet juist.

 

[Timshel]

@ rob2b en Ginger.
Voortschrijdend inzicht

=SOMPRODUCT(ISGETAL(VERGELIJKEN(INDIRECT("'dag"&$B$1&"'!$C$8:$C$19");B$3:B$12;0))*INDIRECT("'dag"&$B$1&"'!$E$8:$E$19"))/AANTALARG(B$3:B$12)

 

[ro2b]

Hier kan ik weer even mee verder.

HELEMAAL SUPER.

groet
RO2B

 

[ro2b]

Oke, ik heb er even een tijdje mee gewerkt en kom toch een "dingetje" tegen.

Als ik een deelnemers toevoeg op de dagen (onderste rij kopiëren en gegevens wijzigen) dan neemt hij dat niet mee in de berekening.
Ik heb hier nu een deelnemerslijst aan toegevoegd die ik dan met een verwijzing opneem per dag. Het moet een variabele lijst zijn.

Volgens mij is het iets met VERSCHUIVING maar kom er niet uit.

Bekijk bijlage gemiddelde van variabele gegevens-2.xlsm

 

[arrie23]

Zoiets? Onderstaande formule plaatsen in cel B14 op tabblad ploegen. Nu kun je, in dit voorbeeld, deelnemers toevoegen op tabblad dag21 en worden deze nieuwe deelnemers meegenomen in de berekening als ze toebedeeld worden aan een ploeg op het tabblad ploegen

=SOMPRODUCT(ISGETAL(VERGELIJKEN(INDIRECT("'dag"&$B$1&"'!$C$8:$C$"&AANTALARG(INDIRECT("'dag"&$B$1&"'!$C:$C"))+5);B$3:B$12;0))*INDIRECT("'dag"&$B$1&"'!$E$8:$E$"&AANTALARG(INDIRECT("'dag"&$B$1&"'!$E:$E"))+5))/AANTALARG(B$3:B$12)

 

[ro2b]

De formule werkt niet helemaal. In mijn voorbeeld die ik hieronder bijvoeg heb ik een ploeg samengesteld met 4 dezelfde deelnemers en kom op een resultaat dat 1/4 is van hetgeen het moet zijn. (zie PLOEG D). Ik zie alleen niet waar het verkeerd gaat

Bekijk bijlage gemiddelde van variabele gegevens-3.xlsm

OEPS: Ik begrijp het al. Een ploeg uit een aantal dezelfde deelnemers ziet deze formule als 1 deelnemer die vaker voorkomt waardoor het gemiddelde lager is. Dat is natuurlijk ook correct want een ploeg bestaat uit uitsluitend verschillende deelnemers

Wat wel anders zou moeten is het aantal deelnemers volgens de lijst met deelnemers ipv per dag.

 

[plongske]

Hoe kan 4 maal dezelfde deelnemer in dezelfde ploeg zitten?