top 5 opeenvolgende getallen zoeken tov berekend getal

[ericabt]

goedenavond,

Ik heb een ogenschijnlijk simpel probleem, maar krijg het niet voor elkaar. In het kort:
Ik heb in een cel een berekend getal. Ergens anders heb ik een kolom vol met willekeurige getallen (niet gesorteerd).
Nu wil ik een top 5 maken van cijfers uit die kolom die het dichtste in de buurt liggen (of gelijk zijn indien aanwezig in de lijst) van het berekende getal.

De kolom met willekeurige getallen, mag niet gesorteerd worden.

In de bijlage een voorbeeldje. Het zou geweldig zijn als iemand de formules in de gele vakken kan zetten

Bekijk bijlage voorbeeld.xlsx

 

[Thoralf]

Is dit wellicht wat je zoekt?

Het uploaden van een bestand gaat even niet, daarom de formule:
in C17:

=KLEINSTE(ABS(G3:G14-A3);1)+65

als matrixformule invoeren, dus met CTRL+SHIFT+ENTER
In C18 de 1 vervangen door een 2 etc.

Aanvulling:
Deze formules werken in het voorbeeld goed, maar moeten aangepast:
de basis moet worden een vergelijken op bestaan van zowel het getal zoals in de formule gegeven als het getal uit de formule maar dan met een min ipv een plus 65 (65 dan natuurlijk wel een celverwijzing).
Dus

=ALS(ISFOUT(VERGELIJKEN(KLEINSTE(ABS(G3:G14-A3);1)+A3;G3:G14;0));A3-KLEINSTE(ABS(G3:G14-A3);1);KLEINSTE(ABS(G3:G14-A3);1)+A3)

als matrixformule met de toevoeging van de wijzigingen.

 

[xlfac]

met vba is dit zo opgelost, maar met formules wordt het lastiger.

 

[xlfac]

Ik heb =KLEINSTE(ABS(G3:G14-A3);1)+65 geprobeerd maar dat werkt niet volgens mij.

 

[Thoralf]

Heb de formule reeds aangepast, zie aanvulling in #2

 

[ericabt]

Is dit wellicht wat je zoekt?

Het uploaden van een bestand gaat even niet, daarom de formule:
in C17:

=KLEINSTE(ABS(G3:G14-A3);1)+65

als matrixformule invoeren, dus met CTRL+SHIFT+ENTER
In C18 de 1 vervangen door een 2 etc.

Aanvulling:
Deze formules werken in het voorbeeld goed, maar moeten aangepast:
de basis moet worden een vergelijken op bestaan van zowel het getal zoals in de formule gegeven als het getal uit de formule maar dan met een min ipv een plus 65 (65 dan natuurlijk wel een celverwijzing).
Dus

=ALS(ISFOUT(VERGELIJKEN(KLEINSTE(ABS(G3:G14-A3);1)+A3;G3:G14;0));A3-KLEINSTE(ABS(G3:G14-A3);1);KLEINSTE(ABS(G3:G14-A3);1)+A3)

als matrixformule met de toevoeging van de wijzigingen.

Dit komt een aardig eind in de richting, in sommige gevallen gaat het goed, echter de absolute waarde zorgt ervoor dat negatieve getallen (dat zijn dus eigenlijk de waardes die kleiner zijn dan mijn berekend getal en dus niet mee mogen doen) positief worden en dus ook mogelijk mee gaan doen. Je wilt eigenlijk dat de negatieve getallen niet meedoen en dat je dus vanaf 0 begint te tellen.

 

[oeldere]

Niet de mooiste oplossing (met hulpkollommen) (zie bijlage).

 

[ericabt]

Heb de formule reeds aangepast, zie aanvulling in #2

als ik nu dit in het eerste vak invul en dan a3 op 100 zet, krijg ik 93, dat gaat dus nog niet helemaal goed. ben even bezig je formule aan het ontrafelen.

 

[HSV]

Probeer het eens.
Matrixformule.

=ALS(AANTAL.ALS($G$3:$G$14;">="&$A$3)<RIJ()-16;"";KLEINSTE(ALS($G$3:$G$14>=$A$3;$G$3:$G$14);RIJ()-16))

 

[Thoralf]

waarom is 93 niet goed? Je wilt immers de getallen die het dichtst in de buurt liggen? en 93 ligt van de getallen het dichtst bij 100.

 

[ericabt]

deze werkt inderdaad volgens mij wel goed. Het is inderdaad wel jammer dat ik hulpcellen nodig heb, temeer omdat ik die willekeurige lijst in diverse tabs wil gaan aanspreken en dan iedere keer dit soort constructies moet gaan bouwen.

Dus mocht er nog een slimme ingeving komen om die hulpkolommen te omzeilen, dan is dat welkom.

Anders een voorstelletje met een macro, als dat bijvoorbeeld wel heel simpel kan?

Maar toch alvast hartelijk bedankt. Zover ben ik zelf nog niet gekomen.

 

[ericabt]

waarom is 93 niet goed?

ik wil de eerste vijf getallen groter of gelijk aan de waarde in cel A3 hebben. Dus als ik daar 100 invoer, dan is 93 kleiner en dat is dus niet de bedoeling

 

[ericabt]

Probeer het eens.
Matrixformule.

=ALS(AANTAL.ALS($G$3:$G$14;">="&$A$3)<RIJ()-16;"";KLEINSTE(ALS($G$3:$G$14>=$A$3;$G$3:$G$14);RIJ()-16))

Super, volgens mij is dit hem. Ik ga hem even doorgronden en dan kan dit afgesloten worden.

 

[oeldere]

@ericabt

Tip voor de toekomst, geef in je post even aan op welke reactie (postnummer) je reageert.

 

[Thoralf]

Dan nu wellicht de goede oplossing: In C17, als matrix-formule:

=KLEINSTE(ALS($G$3:$G$14>=$A$3;$G$3:$G$14;"");B17)

en deze doortrekken naar beneden.

 

[oeldere]

@Thoralf

Je formule werkt.

Ik vind dit een zeer mooie oplossing. :thumb:

 

[HSV]

Die is juist Thoralf, op een kleinigheidje na.
Mochten er geen getallen zijn die groter zijn dan A3, verschijnt er #getal! in de cel.
Dit heb ik opgelost door het eerste gedeelte in mijn formule.

 

[ericabt]

Bedankt voor het antwoord, dit had ik zelf niet verzonnen. Alleen nu realiseer ik me dat van de gevonden getallen (nr 1 -5) ik ook informatie wil hebben die bijv een kolom ernaast staat in de overeenkomstige rij.
Dus in een aanvullend voorbeeld (zie bijlage) heb ik in naastgelegen kolom de letters A t/m L opgenomen (kan straks van alles zijn).
Bij 65 hoort dus in dit geval B, dat zou ik dan terug willen zien naast de resultaten 1 t/m 5 in de gele vlakken (in de lichtblauwe zone bijv)

Is dit nog eenvoudig voor elkaar te krijgen? Daarna laat ik iedereen weer met rust
Bekijk bijlage voorbeeld_aanvulling.xlsx

 

[oeldere]

@HSV

- baas boven baas -

Je formule werkt.

Ik vind dit een zeer mooie oplossing. :thumb:

 

[oeldere]

Als het mag met een hulpkolom kan het met deze

=INDIRECT("F"&VERGELIJKEN($C17;$I$3:$I$14;0)+2)

Meteen erbij vertellen, dat met veel data het gebruik van de functie indirect ongewenst is, omdat het bestand hier traag van wordt.