Som beste vier resultaten uit reeks van zes

[Gert-Jan]

L.S.

Is het mogelijk om uit zes uitslagen alleen de beste vier resultaten op te tellen? Zie bijlage.

In een petanquecompetitie wordt er zes dagen gespeeld. Per dag worden er vier wedstrijden gespeeld.
De score voor een dag wordt aangegeven met WP (aantal gewonnen partijen) en Sld ( het saldo over deze partijen.

Het is eenvoudig om alle dagen bij elkaar op te tellen. Maar nu tellen de vier beste resultaten over de hele competitie.
Kan dit in een formule worden gedaan.

In de bijlage zijn de 'rode' resultaten niet meegeteld. Deze zijn handmatig uit de optelling verwijderd.

[RPG]

Hallo

Er bestaat een functie grootste. Je kunt hier een bepaald getal uit een reeks halen. Gezien het feit dat je de vier grootste wilt hebben moet dat vier keer gebeuren.

Het wordt speciaal moeilijk gezien het feit dat de getallen niet in een enkel bereik staan. Je moet ze eerst in een enkel bereik plaatsen.

Het resultaat is de volgende functie:

Code: Selecteer alles

=GROOTSTE(C8~E8~G8~I8~K8~M8;1) + GROOTSTE(C8~E8~G8~I8~K8~M8;2) + GROOTSTE(C8~E8~G8~I8~K8~M8;3) + GROOTSTE(C8~E8~G8~I8~K8~M8;4)

Het lijkt mij de zelfde uitkomst te geven als wat in kolom O staat.

De oplossing voor kolom R weet ik (nog) niet.

Romke

[Gert-Jan]

Hoi Romke,

De formule die je voorstelt werkt inderdaad voor de beste 4 dagen met wp.
Nu nog kolom R.
Je hebt me al enorm geholpen. Als je deze oplossing ook vindt, hoor ik het graag.
Zelf ga ik ook puzzelen.

HG Gert-Jan

[RPG]

Hallo

Ik begrijp niet de telling voor de andere kolom maar het lijkt ook zo te werken. Ik heb het geprobeerd en ik krijg andere uitkomsten. Maar dit kan ook veroorzaakt worden door het feit dat soms vijf cellen worden opgeteld.

Romke

[Gert-Jan]

Hoi Romke,

Je hebt gelijk.
Met sorteren/plakken heb ik een fout gemaakt. En daardoor zijn sommige resultaten over 5 dagen berekend.
Hierbij alsnog het juiste bestand met een eindstand over zes dagen en over vier dagen.

Ik hoop dat je formule voor kolom R dan wel klopt.

Mvg Gert-Jan

[Gert-Jan]

Sorry,

Hier alsnog het bestand.

[RPG]

Hallo

Ik heb de formules in gebracht maar ik heb nog op een plek een fout.

Romke

[Gert-Jan]

Hoi Romke,

Ik denk dat dit komt omdat de formule de laagste twee saldo's eruit filtert.
Maar het resultaat van 22 mei met 1WP sld -10 is slechter dan het resultaat van 24 juli met 2WP sld -13.
Ik denk dat de WP goed geteld worden, maar dat bij saldo de slechtste resultaten los van de wp
bekeken worden. Het hoort een combinatie te zijn van wp met het bijbehorende saldo.

Alvast heel erg bedankt voor je inzet tot nu toe.

HG Gert-Jan

[RPG]

Hallo

Ik heb geen idee hoe de punten telling verloopt maar ik heb het idee dat -13 slechter is dan -10.

Een voorbeeld om het duidelijk te maken.
Ik heb liever -10 euro (dat is dus schuld of geld te kort) dan -13 euro.

Romke

[Gert-Jan]

Ik begrijp dat je dit denkt. Maar met saldo wordt hier geen geld bedoeld, maar meer doelsaldo als in voetbal.
Het aantal gewonnen partijen gaat boven het 'doelsaldo'. Dus 2 gewonnen partijen zijn altijd beter dan 1 gewonnen partij. Het 'doelsaldo' is er alleen om een verschil te maken bij een gelijk aantal gewonnen partijen. Vergelijkbaar met doelsaldo in het voetbal.
Dus 1wp en -5 saldo is beter dan 1WP en - 10 saldo. Maar 2WP is altijd beter dan 1 WP, ongeacht het saldo. Dus in dit geval is 2WP met -13 sld beter dan 1WP met -10 sld.

Jouw formule werkt perfect bij een gelijk aantal gewonnen partijen. Bij een ongelijk aantal gewonnen partijen jammer genoeg nog niet, omdat een heel enkele keer men met 1 gewonnen partij een beter saldo heeft dan met 2 gewonnen partijen. Of met 2 gewonnen partijen een beter saldo heeft dan met 3 gewonnen partijen.

In deze tabel gebeurt dit slechts eenmaal. En in een grotere uitslag van 30 equipes gebeurde het zelfs helemaal niet. Dus dat is gemakkelijk te controleren, als je kan zien welke scores niet zijn meegeteld. Daarom is de tekstkleur 'rood' bij de niet meegetelde scores. Dan kan de wedstrijdleider, maar ook de speler, gemakkelijk controleren welke scores niet zijn meegeteld.
Dit is handmatig gedaan. Kan dit automatisch aangepast worden? En zo ja, met welke functie, dan ga ik daar zelf zoeken.

In ieder geval heel erg bedankt voor je hulp.

[RPG]

Hallo

Ik heb een andere methode gevonden om het gewenste te bereiken. Wel is het zo dat ik een andere werkwijze gevolgd heb. Ik heb alleen nog maar het resultaat. Je moet zelf kijken hoe je het resultaat wilt tonen.

Beschrijving
Ik heb eerst alle resultaten in een simpele tabel gezet. Het is zo dat ik die tabel heb afgestemd op het huidige probleem.

Database bereik
Het bleek zo te zijn dat een databereik van de tabel het gebruik van match en offset vereenvoudigde. Ik hoefde geen correctie toe te passen in Offset. Bovendien is het al met absolute adressering. Het is beter om dat te definieren want dan worden gegevens bewaard

Sorteren
Om het gewenste te kunnen bereiken moet de tabel goed gesorteerd worden. En wel op team, WP en saldo

Match
Met match kan gezocht worden waar het gewenste begint.

Count
Ik wil wel graag weten of er vier getallen beschikbaar zijn. Bij aanpassen gebruik ik het maximale aantal. Ik heb niet gecontroleerd of het goed werkt op de beperking.

Offset
Offset is een krachtige maar daardoor ook een ingewikkelde functie. Het is mogelijk om delen uit een grote tabel te knippen en daar een bewerking op doen. In dit voorbeeld knip ik een klein uit de tabel en wel dat deel wat hoort bij het team en dan ook slechts de eerste vier waarden. De vier waarden kunnen zijn WP of saldo.

Tussen stappen
Zo als je ziet staan alle tussen stappen er in. Dat doe ik om de formule te kunnen samen stellen. In begin weet ik wat de methode is waar ik naar toe wil werken. Ik maak dan een formule voor elke stap. Ik kan kijken of die stap goed is en verder gaan. Als blijkt dat later er toch nog een fout is dan kan ik gemakkelijker een tussen stap corrigeren dan een formule van 250 tekens waarbij ik niet weet waar het ene deel van de formule begint of eindig.

Engels naar Nederlands
offset= verschuiving
Match = vergelijken
aantal.als = countif



Algemeen idee
Mijn persoonlijk idee is dat zoiets altijd begint met het verzamelen van ruwe gegevens. In verschillende stappen worden de resultaten uit de gegevens gehaald. De laatste stap is het tonen van de gegevens.

Puntentelling
De punten telling wordt duidelijker voor. Je laatste bericht kwam binnen toen ik dit aan het schrijven was. Het automatisch aanmerken van niet mee te tellen uitslagen lijkt mij een zelfde probleem als waar we nu naar kijken. Het kan best zo zijn dat iemand met een andere kijk naar het probleem het wel gemakkelijk kan oplossen. Ik heb de neiging te denken in kleine tussen stappen en tabellen. Als laatste denk ik vaak pas aan de vormgeving.

Romke

[Gert-Jan]

Hallo Romke,

Deze techniek kende ik nog niet. Ik ga het eerst goed bestuderen voordat ik kan reageren.

Bedankt, weer.

Gert-Jan

[eremmel]

Het grootste probleem lijkt te zitten in de telling (WP, Sld) Je kunt hier makkelijk een getal van maken. Als de uitkomst van Sld altijd binnen [-49,+49] is zou je elke score om kunnen zetten in:
WP*100 + 100/2 + Sld. Of neem 1000 ipv 100.
Je krijgt van 6 op een volgden velden: S8:X8 etc. Deze kun je dan makkelijk binnen LARGE() gebruiken.

Door slim van een array formule gebruik te maken kun je een array-resultaat krijgen van de hoogste 4 scores: =LARGE(S8:X8,{1,2,3,4})
Deze array moet voor het totaal van WP weer gedeeld worden door 100 (zonder rest) en dan opgeteld worden: =SUM(INT(LARGE(S8:X8,{1,2,3,4})/100))
Voor Sld moeten we modulo 100 rekenen en vervolgens er 50 aftrekken per array element en dat weer optellen: =SUM(MOD(LARGE(S8:X8,{1,2,3,4}),100)-50)

Note: Bij het invoeren van een array formule moet je niet afsluiten met enkel 'enter' maar met 'Shift-Ctrl Enter' Zie help.

[Gert-Jan]

Hallo eremmel,

Deze oplossing is fantastisch. Het was een paar dagen puzzelen voor ik de matrixformule begreep, maar ik snap nu het meeste.
Ik heb je oplossing in een competitie over 9 dagen, waarbij de beste 6 dagen meetellen, getest en dit ging heel goed. Alhoewel er een paar verschillen ontstonden, die wel logisch te verklaren zijn. Zie bijlage.

Ik heb nog wel een paar vragen:

- Op een toernooidag is het theoretisch mogelijk dat een team alle vier wedstrijden met 13-0 wint danwel verliest. Dan zou dit team die dag 4WP +52 of 0WP en -52 sld hebben. Je schrijft dat het binnen [-49,+49] moet zijn. Is er dan een probleem of kan je dit opvangen met het vergroten van de factor en de bias?

- In jouw formule voor een geheel getal staat factor en bias vermeldt. De factor geeft de waarde van cel Z1 (100) en de bias de waarde van cel Z2 (de helft van Z1 (50)). Ik kreeg dit alleen voor elkaar met een absolute celverwijzing. $Z$1. Hoe kan ik het woord 'factor' en 'bias' in de formule weergeven en daar de waarde aan toe wijzen?

- In de bijlage zijn er 9 speeldagen, waarbij de beste 6 resultaten meetellen. Nu speelt niet ieder team alle speeldagen en zijn er dus blanke scores. De getalformule geeft dan logisch een getal van 50, de bias. Bij de optelling van het aantal WP, maakt dit niet uit. De formule klopt. Bij de optelling voor het saldo over 6 dagen, gaat het echter fout. Omdat 50 groter is dan bij een dagscore van 0WP met een negatief saldo en dus meegeteld wordt. Hoe kan dit gecorrigeerd worden, zodat niet gespeelde dagen niet meegeteld worden?

- Tenslotte is het mogelijk om de niet meegetelde scores in de uitslagentabel een andere achtergrond te geven, zodat men gemakkelijk kan zien, welke scores niet zijn meegeteld?

Ik hoop dat je kunt helpen.
Gert-Jan

[RPG]

Hallo

Voor sommige vragen is het raadzaam om alles van Eremmel in deze draad op nieuw te lezen.

Hoe kan ik het woord 'factor' en 'bias' in de formule weergeven en daar de waarde aan toe wijzen?

menu --> Invoegen -->Namen --> Definieren

Je daar ook een een naam invoegen. Daar na kun je kiezen om aan deze naam wat toe te kennen. Dit kan zijn:

• Een waarde
  Een cel
  Een cel bereik
  Een formule
  En misschien nog wel meer

Romke

[eremmel]

Gert-Jan,
Mijn complimenten dat je array-formules hebt opgepakt! Dit is heel krachtig maar niet eenvoudig zoals je al gemerkt hebt, het is altijd een risico om deze advanced features voor te stellen voor iemand die nog maar net op het forum komt.

- Op een toernooidag is het theoretisch mogelijk dat een team alle vier wedstrijden met 13-0 wint danwel verliest. Dan zou dit team die dag 4WP +52 of 0WP en -52 sld hebben. Je schrijft dat het binnen [-49,+49] moet zijn. Is er dan een probleem of kan je dit opvangen met het vergroten van de factor en de bias?

Ja maak de factor, bias 1000, 1000/2

- In jouw formule voor een geheel getal staat factor en bias vermeldt. De factor geeft de waarde van cel Z1 (100) en de bias de waarde van cel Z2 (de helft van Z1 (50)). Ik kreeg dit alleen voor elkaar met een absolute celverwijzing. $Z$1. Hoe kan ik het woord 'factor' en 'bias' in de formule weergeven en daar de waarde aan toe wijzen?

Naast het antwoord van Romke kun je aan een enkele cel snel een andere naam geven. Selecteer de cel die je een naam wilt geven bv Z1, dan zie je links boven ook Z1 staan: daar kun je nu een nieuwe naam invullen.

- In de bijlage zijn er 9 speeldagen, waarbij de beste 6 resultaten meetellen. Nu speelt niet ieder team alle speeldagen en zijn er dus blanke scores. De getalformule geeft dan logisch een getal van 50, de bias. Bij de optelling van het aantal WP, maakt dit niet uit. De formule klopt. Bij de optelling voor het saldo over 6 dagen, gaat het echter fout. Omdat 50 groter is dan bij een dagscore van 0WP met een negatief saldo en dus meegeteld wordt. Hoe kan dit gecorrigeerd worden, zodat niet gespeelde dagen niet meegeteld worden?

Je moet dan onderscheid maken tussen 'leeg' veld en een veld met score 0 WP. Dit kun je doen in de formule door het volgende te doen: =IF(ISBLANK(D8); 0; D8*$AY$1+$AY$2+E8). Op deze manier krijgt een niet gespeelde dag de laagste waarden die mogelijk is nl. 0.

- Tenslotte is het mogelijk om de niet meegetelde scores in de uitslagentabel een andere achtergrond te geven, zodat men gemakkelijk kan zien, welke scores niet zijn meegeteld?

We hebben de formule om de top 6 waarden te berekenen. We kunnen daarmee ook de minimale waarde berekenen uit die serie van de top 6:
=MIN( LARGE ($AO8:$AW8,{1,2,3,4,5,6}))
Maar dit is nog steeds een array formule. Daar kan conditional-format niet mee overweg. Maar als we die wrappen in SUMPRODUCT() krijgen we netjes een enkele waarde:
=SUMPRODUCT( MIN( LARGE ($AO8:$AW8,{1,2,3,4,5,6}) ) )
Deze waarde kunnen we vergelijken met de huidige waarde binnen een conditional-format. Als de waarde van een cell < aan de minimale waarde van de hele rij dan telde die niet mee. Met deze aanwijzing moet het lukken lijkt me.

[Gert-Jan]

eremmel,

Pff. Maar bedankt weer. Ik ga aan de slag.
De laatste twee antwoorden vragen nog even tijd, maar je hoort van me.

Inmiddels heb ik de formule ook "vereenvoudigd" met 'ALS', althans de formule is langer, maar sluit
de teams met 6 of minder toernooidagen uit. Daarvoor wordt het generaal resultaat
over zes speeldagen gegeven. Zie bijlage.

De formule voor saldo is nog steeds niet goed. Maar er zit nog maar 1 foutje in.
Dat probeer ik op te lossen met je 'lege cellen' antwoord.

Tenslotte, zou je de formule in AZ8 in de bijlage willen controleren? Op dit moment
wordt AI8 onnodig opgeteld in het resultaat. Ik denk dat dit om een haakje ergens gaat.

Ik hoor graag van je en bedankt voor je hulp.

HG Gert-Jan


Bedankt weer.

HG Gert-Jan

[Gert-Jan]

Hallo eremmel,

Alles is gelukt. De laatste vraag in de vorige reactie is niet meer van toepassing.
Alle scores zijn goed en niet meegetelde resultaten worden rood.

Er zit nog 1 foutje in bij Ron Bakker, rij 43. Dit komt omdat Ron tweemaal niet heeft gespeeld en zijn twee slechtste resultaten zijn gelijk (0WP - 28). Eén van deze scores zou rood aangegeven moeten worden, maar dat gebeurt niet. Als ik bij een van beiden 0WP - 29 invoer is het uiteraard goed.

Is er een mogelijkheid om dit ook nog op te vangen.

In ieder geval ontzettend bedankt voor je hulp. Ik heb er ontzettend veel van geleerd. Ook van Romke heb ik veel geleerd, wat in de toekomst nog zeker van pas gaat komen.

Gert-Jan

[eremmel]

Hallo eremmel,

Alles is gelukt. De laatste vraag in de vorige reactie is niet meer van toepassing.
Alle scores zijn goed en niet meegetelde resultaten worden rood.

Er zit nog 1 foutje in bij Ron Bakker, rij 43. Dit komt omdat Ron tweemaal niet heeft gespeeld en zijn twee slechtste resultaten zijn gelijk (0WP - 28). Eén van deze scores zou rood aangegeven moeten worden, maar dat gebeurt niet. Als ik bij een van beiden 0WP - 29 invoer is het uiteraard goed.

Is er een mogelijkheid om dit ook nog op te vangen.

In ieder geval ontzettend bedankt voor je hulp. Ik heb er ontzettend veel van geleerd. Ook van Romke heb ik veel geleerd, wat in de toekomst nog zeker van pas gaat komen.

Gert-Jan

Gert-Jan,
Altijd leuk om te zien dat iemand redelijk zelfstandig door zo'n steile leercurve komt en daar z'n eigen oplossingen weer aan toevoegt.

Mbt je overgebleven vraag:
Je hebt dubbele waarden die gelijkwaardig worden bekeken. Je zou een ordening moeten maken zodat je 'weet' welke van de waarden in de top 6 zitten. In theorie kan iemand 9x spelen en dan ook 9x de zelfde uitslag hebben. Je wilt dan uiteindelijk 3 rode waarden zien uit de 9.

Een mogelijkheid is om de 9 spelen zelf ook te nummeren en dat te gebruiken. Dus iets als
PW x bias x 10 + (sld+bias/2) x 10 + spelnummer.
Waarbij je spelnummer zou kunnen bepalen door te kijken naar het kolom nummer, bv (colom(cel-ref-spel) - column(cel ref eerste spel) ) / 2
Je formule samengestelde PW resultaat gaat een beetje veranderen; je samengestelde sld formule moet je nog weer corrigeren voor de x 10 (en wel voordat je gaat optellen!)

Ik denk dat je met deze oplossingsschets er wel uit moet kunnen komen. Ik heb het zelf ook nog niet geprobeerd, dus het zou lastiger zijn dan ik denk...