Excel: formule voor het afronden van getal naar een waarde in een tabel

[lucabcd]

E.Desart,

Ik bedoel met mijn vraag:

Ik heb als uitkomst van twee sommen dus twee waarden,

ik wil nu dat excel de waarde die het dichtste in de buurt van een waarde uit de lijst kiest,

en die afrond op de desbetreffende waarde...

Verder hoef ik niet te weten welke van de twee waardes dat zijn, enkel de uitkomst is intressant,

Volgens mij had je het dus redelijk goed,

echter als ik de formule die je gaf wil invoeren als array formule krijg ik een foutmelding...

[E.Desart]

• Jij hebt een Nederlandstalige Excel?
• Jouw seperator is die punt-komma?
• Jij hebt die accolades NIET getypt? Je moet die niet mee kopiëren omdat die automatisch komen als je de formule juist entert.

Even herhalen array formule procedure:

Je kopieert deze formule (is mijn formule zonder accolades)

=ALS(MIN(ABS(A1:A7-B1))<MIN(ABS(A1:A7-C1));ALS(B1<A1;A1;INDEX(A1:A7;VERGELIJKEN(B1+MIN(ABS(A1:A7-B1));A1:A7)));ALS(C1<A1;A1;INDEX(A1:A7;VERGELIJKEN(C1+MIN(ABS(A1:A7-C1));A1:A7))))

Je selecteert formule. (Cursor in formule)

Je drukt de toetsen Ctrl + Shift + Enter in

Dan wordt deze formule automatisch (in de formulebar):

{=ALS(MIN(ABS(A1:A7-B1))<MIN(ABS(A1:A7-C1));ALS(B1<A1;A1;INDEX(A1:A7;VERGELIJKEN(B1+MIN(ABS(A1:A7-B1));A1:A7)));ALS(C1<A1;A1;INDEX(A1:A7;VERGELIJKEN(C1+MIN(ABS(A1:A7-C1));A1:A7))))}

Mijn formule werkt wel (bovenstaand is gelijk aan formule vorige post).

Jij doet ergens iets fout. Probeer terug en vertel welke fout je krijgt als het niet lukt.

[lucabcd]

Je hebt gelijk de formule werkt wel, sorry ben niet zo handig met excel,

ik heb de ; vervangen door , en nu werkt het,

E.Desart, hardstikke bedankt voor je tijd en geduld om mij het een en ander uit te leggen!!

mvg Luc

[E.Desart]

ik heb de ; vervangen door , en nu werkt het,

Graag gedaan

](*,) Dat is een nieuwe voor mij: Iemand met een Nederlandstalige Excel met Amerkaans/Engelse separator instelling.

Jij hebt vermoedelijk/mogelijk Nederlandstalige programmas in een Engelse Windows? (of iemand heeft de standaard instellingen in de Nederlandstalige Windows gewijzigd).

Als je dergelijke info op voorhand vertelt (taal en separator) kunnen mensen die je helpen hier direct rekening mee houden.

{=ALS(MIN(ABS(A1:A7-B1))<MIN(ABS(A1:A7-C1)),ALS(B1<A1,A1,INDEX(A1:A7,VERGELIJKEN(B1+MIN(ABS(A1:A7-B1)),A1:A7))),ALS(C1<A1,A1,INDEX(A1:A7,VERGELIJKEN(C1+MIN(ABS(A1:A7-C1)),A1:A7))))}

:eusa_whistle: Dit is deze formule in het Russisch:

{=ЕСЛИ(МИН(ABS(A1:A7-B1))<МИН(ABS(A1:A7-C1));ЕСЛИ(B1<A1;A1;ИНДЕКС(A1:A7;ПОИСКПОЗ(B1+МИН(ABS(A1:A7-B1));A1:A7)));ЕСЛИ(C1<A1;A1;ИНДЕКС(A1:A7;ПОИСКПОЗ(C1+МИН(ABS(A1:A7-C1));A1:A7))))}

[lucabcd]

Ja klopt, heb Engelse windows maar de taal ingesteld in het Nederlands, zal het de volgende keer eerder vermelden :eusa_whistle:

heb weer een nieuwe uitdaging:

Ik ben de formule steeds verder aan het uitbereiden, heb nu een nieuw gegeven wat er als het mogelijk is ook in verwerkt moet worden,

zal eerst de situatie even uitleggen:

de getallen in het rijtje (A1:A7) zijn gestandaardiseerde verpakkingshoeveelheden,

B1 en C1 zijn twee manieren waarop tot een goede verpakkingshoeveelheid kan worden gekomen,

nu is er echter een beperking: de verpakkingshoeveelheid mag niet zwaarder zijn als 23 kilo = maximale verpakkingshoeveelheid: (23 kilo / gewicht per stuk) = D1

de uitkomst van de formule mag dus niet meer zijn dan de waarde die in D1 gegeven wordt,

als er dus uit de vorige formule een uitkomst komt van 250, en D1 geeft aan dat er niet meer artikelen verpakt mogen worden dan 230, moet de formule een waarde onder de 230 aangeven, in dit geval dus 150.

hoop dat bovenstaande een beetje duidelijk is en inzicht geeft in de situatie...

[E.Desart]

Staan de getallen B1 en C1 steeds in stijgende volgorde? Dus B1 steeds een lager getal?

Maak een duidelijk onderscheid tussen < en <=

Hoe komt in je voorbeeld voor D1 zowel 23 als 230 voor?

Hoe relateren deze zinnen:

nu is er echter een beperking: de verpakkingshoeveelheid mag niet zwaarder zijn als 23 kilo = maximale verpakkingshoeveelheid: (23 kilo / gewicht per stuk) = D1

als er dus uit de vorige formule een uitkomst komt van 250, en D1 geeft aan dat er niet meer artikelen verpakt mogen worden dan 230, moet de formule een waarde onder de 230 aangeven, in dit geval dus 150.

In het 1ste geval praat je over gewicht per stuk, in het 2de over over een aantal artikelen

[lucabcd]

De getallen in B1 en C1 staan niet in stijgende volgorde maar zijn variabel, de ene keer is B1 hoger de andere keer C1,

Een verpakking mag maximaal 23 kilo wegen (gewicht van de verpakking is niet meegerekend), door dit getal te delen door het gewicht per stuk krijg je het maximale aantal artikelen dat er in een verpakking verpakt mag worden (D1),

nu mag de uitkomst van de formule niet meer zijn dan de waarde uit D1,

als de uitkomst dus wel hoger is als de waarde uit D1 zal de uitkomst na beneden moeten worden afgerond tot een waarde uit de lijst (A1:A7) die wel geschikt is,

als de uitkomst uit D1 = 230 stuks

en de uitkomst uit de oorspronkelijke formule is 250,

de uitkomst uit de nieuwe formule moet dan zijn 150, omdat de uitkomst niet meer mag zijn als 230

[E.Desart]

Een verpakking mag maximaal 23 kilo wegen (gewicht van de verpakking is niet meegerekend), door dit getal te delen door het gewicht per stuk krijg je het maximale aantal artikelen dat er in een verpakking verpakt mag worden (D1),

In welke cel staat dan die 23 kilo?

[lucabcd]

die 23 kilo staat in een andere cel, het is namelijk een variabele, maar dat is niet van belang toch??

[E.Desart]

Kan best zijn, maar jij vermelde het, ik weet niet waar jij met bezig bent en moet aldus proberen te snappen wat er juist gebeurt, en wat er verwacht wordt.

Geef toe dat dit verwarrend is: (23 kilo / gewicht per stuk) = D1.

Het is duidelijker wanneer je gewoon vertelt wat een formule exact moet doen, waarbij je rekening dient te houden met wat er dient te gebeuren met

1. afrondingen naar boven en naar onder
2. de noties >, <, <=
3. het feit of lijsten (of argumenten indien van toepassing) stijgend, dalend of willekeurig zijn
4. enz.

Het willekeurig zijn van de waarden B1 en C1 (punt 3.) bijv. is nu een nieuwe informatie waar ik de formule dien aan aan te passen.

En antwoord a.u.b. op al mijn opmerkingen

{=ALS(MIN(ABS(A1:A7-B1))<MIN(ABS(A1:A7-C1));ALS(B1<A1;A1;INDEX(A1:A7;VERGELIJKEN(B1+MIN(ABS(A1:A7-B1));A1:A7)));ALS(C1<A1;A1;INDEX(A1:A7;VERGELIJKEN(C1+MIN(ABS(A1:A7-C1));A1:A7))))}

Die < kan ook <= worden afhankelijk welke waarde je voorkeur geeft bij gelijk verschil.

Als je nu zegt of je hier < (kleiner) of <= (kleiner dan en gelijk aan) kiest maakt een en ander al duidelijker zeker met de bijkomende aanpassingen en uitbreidingen van de formule. Dus vertel of dat het resultaat bij GELIJK VERSCHIL de hoogste of de laagste tabelwaarde moet geven.

Jij bent met algemene principes bezig. Ik moet het vertalen naar exacte formules. Ik heb alleen jouw woorden als basis, niet wat in jouw hoofd zit.

[E.Desart]

Dus vertel of dat het resultaat bij GELIJK VERSCHIL de hoogste of de laagste tabelwaarde moet geven.

Geef hier even antwoord op en ik kan weer wat verder.

[lucabcd]

Sorry klopt helemaal wat je zegt, heb vanalles in me hoofd zitten waardoor het voor mij duidelijk lijkt maar voor een iemand die er niet mee bezig is, is het zeer onduidelijk,

en (23 kilo / gewicht per stuk) = D1 is inderdaad verwarrend,

Bij een gelijk verschil mag er na boven worden afgerond,

maar is het inmiddels duidelijk of zal ik het nog een keer uitleggen?

[E.Desart]

maar is het inmiddels duidelijk of zal ik het nog een keer uitleggen?

Geef mij gewoon wat tijd nu. Moet ff weg.

[E.Desart]

Werkt dit zoals je wil?

Weer als array invoeren. Separator reeds aangepast, D1 aantal stuks, willekeurigheid B1 en C1 aangepast.

{=ALS(ALS(MIN(ABS(A1:A7-MIN(B1,C1)))<MIN(ABS(A1:A7-MAX(B1,C1))),ALS(MIN(B1,C1)<A1,A1,INDEX(A1:A7,VERGELIJKEN(MIN(B1,C1)+MIN(ABS(A1:A7-MIN(B1,C1))),A1:A7))),ALS(MAX(B1,C1)<A1,A1,INDEX(A1:A7,VERGELIJKEN(MAX(B1,C1)+MIN(ABS(A1:A7-MAX(B1,C1))),A1:A7))))>D1,INDEX(A1:A7,VERGELIJKEN(D1,A1:A7)),ALS(MIN(ABS(A1:A7-MIN(B1,C1)))<MIN(ABS(A1:A7-MAX(B1,C1))),ALS(MIN(B1,C1)<A1,A1,INDEX(A1:A7,VERGELIJKEN(MIN(B1,C1)+MIN(ABS(A1:A7-MIN(B1,C1))),A1:A7))),ALS(MAX(B1,C1)<A1,A1,INDEX(A1:A7,VERGELIJKEN(MAX(B1,C1)+MIN(ABS(A1:A7-MAX(B1,C1))),A1:A7)))))}

Als je iets anders bedoeld als resultaat zeg het dan gerust.

:eusa_whistle: Is het de bedoeling dat je na nog verdere uitbreidingen uiteindelijk op je maandsalaris uitkomt?

[E.Desart]

Sorry, deze formule is een pak korter/efficiënter (was te snel, heb dit eens rustiger bekeken):

Weer als array invoeren:

{=MIN(ALS(MIN(ABS(A1:A7-MIN(B1,C1)))<MIN(ABS(A1:A7-MAX(B1,C1))),ALS(MIN(B1,C1)<A1,A1,INDEX(A1:A7,VERGELIJKEN(MIN(B1,C1)+MIN(ABS(A1:A7-MIN(B1,C1))),A1:A7))),ALS(MAX(B1,C1)<A1,A1,INDEX(A1:A7,VERGELIJKEN(MAX(B1,C1)+MIN(ABS(A1:A7-MAX(B1,C1))),A1:A7)))),MAX(A1,INDEX(A1:A7,VERGELIJKEN(MAX(A1,D1),A1:A7))))}

PS: In mijn vorige formule zat nog een minifoutje: als je als aantal (D1) een getal invoerde dat kleiner was dan het kleinste getal in de tabel (A1:A7) gaf hij een fout. Dit heb ik simpel opgelost, maar aangezien deze formule beter is laat ik de vorige voor wat hij is.

Als je zou willen dat de beperking door D1 niet alleen met het resultaat (kleinste verschil resulterend uit B1 en C1) maar ook terug met het 2de kleinste verschil zou willen vergelijken moet ik een custom functie maken (is véél efficiënter en rekent sneller en ziet er beter uit). Anders wordt de formule zeer uitgebreid en moet deze dezelfde sub-variabelen meerdere keren berekenen.