Vert.zoeken binnen een bepaalde periode

[berny1978]

Beste mensen,

Voor het golfen ben ik een excel-bestand aan het schrijven, maar daarbij dien ik verticaal te kunnen zoeken binnen een periode van een jaar. Alles wat buiten dat jaar valt, moet genegeerd worden.
In het tabblad "Kaarten" in het bijgevoegde voorbeeld, moet ik de laagste WHS zien te vinden binnen een periode van een jaar.
Op basis daarvan moet ik dan weer een nieuwe formule gaan maken.
Maar hoe krijg ik het voor elkaar dat ik dus de juiste kaarten kan filteren, dat ik alleen de kaarten overhoudt die binnen dat jaar vallen? Zonder dat ik deze zou moeten verwijderen.

In de hoop dat iemand me hierbij kan helpen.
Alvast bedankt voor de moeite.

Groeten, Micha

PS: alle tabbladen zijn beveiligd, maar zonder wachtwoord

 

[AlexCEL]

En uit die brei van gegevens moeten wij gaan gokken wat je nu precies waar wilt?

Waar staat de periode waarbinnen je wilt zoeken?
Waar moet de uitkomst komen?
Heb je wat verwachte uitkomsten ergens? Waarom die uitkomst?

 

[berny1978]

En uit die brei van gegevens moeten wij gaan gokken wat je nu precies waar wilt?

Waar staat de periode waarbinnen je wilt zoeken?
Waar moet de uitkomst komen?
Heb je wat verwachte uitkomsten ergens? Waarom die uitkomst?

Wellicht was m'n vorige bericht niet duidelijk, alhoewel het dat voor mij wel was. Sorry.

Zoals je in m'n vorige bericht had kunnen lezen:
Ik wil in het tabblad "Kaarten" in het bijgevoegde bestand;
Uit de kolom "WHS" de laagste waarde vinden;
Die binnen een jaar is gespeeld, waarbij de data (misschien heel verrassend) in de kolom "Datum" staan.

Waar de uitkomst moet komen is even niet zo belangrijk, maar voor nu zou die in de kolom achter de kolom met WHS kunnen komen te staan.

En nee, er is geen verwachte uitkomst, omdat de WHS een speelsterkte is, die per speler anders zal zijn, op basis van zijn behaalde resultaten.
En die uitkomst is benodigd om weer een nieuwe formule op te baseren: als iemand op een gegeven moment dusdanig slecht speelt en de nieuwe WHS-score zou hoger dan 3 punten boven deze laagste waarde uitkomen, dan wordt er een bepaalde remming aangebracht. En de WHS-score mag sowieso nooit lager uitvallen dan 5 punten boven de laagste WHS-score die in een jaar is behaald.

 

[AlexCEL]

Goh, ik had een antwoord verwacht in de trant van: "voor 2020 is de uitkomst 8,79 omdat dat daar maar één waarde voor is gegeven. Voor 2021 verwacht ik in mijn voorbeeld als laagste waarde 8,371."
In jouw voorbeeldje is trouwens de minimale waarde ook de maximale waarde aangezien er maar 1 getal gegeven is voor elk jaar, als ik de 0-waarden even niet meeneem.
Iets meer data mag wel in een voorbeeld om oplossingen te proberen...

Maar ok dan, voor het jaar 2020 zoiets?

=MIN(ALS.FOUT(1/(1/(ALS(JAAR(B2:B151)=2020;K2:K150)));EXP(99)))

Let op: matrixfunctie, d.w.z. afsluiten met Control+Shift+Enter als je geen Excel 365 gebruikt. Doe je dat wel dan kan deze ook:

=MIN(FILTER(K2:K150;(JAAR(B2:B150)=2020)*(K2:K150>0);""))

 

[berny1978]

Goh, ik had een antwoord verwacht in de trant van: "voor 2020 is de uitkomst 8,79 omdat dat daar maar één waarde voor is gegeven. Voor 2021 verwacht ik in mijn voorbeeld als laagste waarde 8,371."
In jouw voorbeeldje is trouwens de minimale waarde ook de maximale waarde aangezien er maar 1 getal gegeven is voor elk jaar, als ik de 0-waarden even niet meeneem.
Iets meer data mag wel in een voorbeeld om oplossingen te proberen...

Maar ok dan, voor het jaar 2020 zoiets?

=MIN(ALS.FOUT(1/(1/(ALS(JAAR(B2:B151)=2020;K2:K150)));EXP(99)))

Let op: matrixfunctie, d.w.z. afsluiten met Control+Shift+Enter als je geen Excel 365 gebruikt. Doe je dat wel dan kan deze ook:

=MIN(FILTER(K2:K150;(JAAR(B2:B150)=2020)*(K2:K150>0);""))

Bedankt voor je hulp tot zover, maar het is nog niet compleet wat ik zoek.
Ik heb extra data toegevoegd, hopelijk dat het nu beter gaat.
En hierbij ook een verdere uitleg wat ik zoek. Sorry dat ik niet heb bedacht dat iets wat voor mij duidelijk is, dat dat niet voor iedereen geldt, omdat er essentiële informatie voor diegene mist. Of dat diegene niet net zo in het spelletje thuis is als ik.

Het gaat erom dat iemand een kaart invult op een bepaalde datum.
Daaruit volgt uiteindelijk een speelsterkte (WHS).
Als er dusdanig slecht is gespeeld dat de WHS 3 punten hoger zou worden dan de laagste waarde van de afgelopen 365 dagen, dan komt er een remmingsfactor in het spel. En als daarna de WHS nog verder zou stijgen tot meer dan 5 punten boven de laagste waarde, dan komt er een stop en blijf je op "die laagste waarde + 5 punten" steken. En stijg je niet verder door, totdat die laagste waarde wegvalt, omdat die buiten de periode van 365 dagen komt.
Het betreft dus geen kalenderjaren.
De WHS moet dus gezocht worden binnen een jaar (365/366 dagen), teruggerekend vanaf de datum die wordt ingevuld wanneer de kaart is ingevuld.

In het nieuwe voorbeeld is de laagste waarde 8,0 tm 15-2-21.
Deze laagste waarde moet dus uit de formule komen voor alle kaarten die tm 15-2-22 worden ingevuld.
Maar voor de kaarten ná 15-2-22 moet er dus een andere waarde uitkomen, afhankelijk van de datum van de ingevulde kaart.
Voor 19-3-22 moet de laagste waarde 12,8 zijn (22-3-21) en voor 25-3-22 zou het 15,1 moeten zijn (25-3-22).

Hopelijk is het nu wat duidelijker wat ik zoek en bedoel. En anders mag het natuurlijk gevraagd worden en probeer ik het nog verder te verduidelijken.

 

[AlexCEL]

Hoop dat ik het begrepen heb. In bijlage een Excel 365 optie aangezien je FILTER gebruikt heb in je voorbeeld. Voor L2:

=LET(
k;K$2:K2;
b;B$2:B2;
x;MIN(ALS.FOUT(1/(1/FILTER(k;(b>=ZELFDE.DAG(B2;-12))*(k>0)*k));EXP(99)));
ALS(x>EXP(98);"";x))

Kun je naar beneden doortrekken voor hopelijk gewenste resultaat. Zie ook bijlage.

N.B. QUOTEN is niet nodig telkens. Gebruik de VEEL grotere knop "reageer op bericht" of tekstvak "snel reageren" onderaan.

 

[OctaFish]

En Benny: je bent al 8 jaar lid van HelpMij; je weet toch ondertussen dat je van de quote knop af moet blijven?

 

[berny1978]

@AlexCEL: bedankt voor je oplossing. Dit lijkt inderdaad hetgeen te zijn wat ik zocht. Ik probeer nu alleen uit te vinden wat het doet en hoe het werkt, zodat ik het later zelf ook zou kunnen opbouwen

@OctaFish: bedankt voor je niets toevoegende opmerking.
A) AlexCEL had dat in de opmerking daarvoor al aangegeven
B) als je iets verder had gekeken, dan had je kunnen opmerken dat ik nauwelijks dingen post of vraag, ondanks dat ik al 8 jaar lid ben. Dus een jaartal zegt niets: iemand kan binnen 8 weken actiever en beter op de hoogte zijn dan een ander in 8 jaar
C) persoonlijk vind ik een quote juist verduidelijken waar je op reageert, zodat daar geen misverstanden over kunnen ontstaan. Dat is in een rustig topic wellicht overbodig, maar in een druk topic lijkt mij dat juist erg zinvol. Anders zouden ze die optie ook wel weg kunnen halen, hè?!

 

[AlexCEL]

LET is een van de nieuwste functies in Excel, soms handig om complexe formules te versimpelen. Dit vond ik wel zo'n geval.
Je kan bereiken, formules e.d. een symbool geven en daarmee verder werken. Dat heb ik gedaan.

FILTER is ook alleen voor Excel 365. Ik filter dus het bereik op de datums tussen die in kolom B en die van een jaar daarvoor (ZELFDE.DAG - 12 maanden). Mocht daar niets of 0 uitkomen dan zorgt de deling (2x om originele resultaat weer terug te krijgen) voor een foutmelding (delen door 0). De ALS.FOUT maakt er dan een groot getal van. Uit de gefilterde lijst zoekt de MIN functie de laagste waarde.

De ALS functie is er voor geval er geen overeenkomsten zijn (alleen grote waarden in de gefilterde lijst), dan krijg je dus een lege cel.