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

[E.Desart]

Het spijt me.

Er zit blijkbaar nog ergens een principiële fout in deze laatste formule. Nog niet gebruiken. Ik kijk hier vandaag terug naar.

[E.Desart]

Dit is wel juist (weer array invoer)

{=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+MIN(ABS(A1:A7-D1))),A1:A7))))}

Het rood was ik vergeten (stom van mij).

:eusa_whistle: 'k heb er nu een simpele macro op losgelaten die 100.000 random combinaties invoerde (geen fouten meer)

Dezelfde opmerking blijft gelden voor uitbreiding als beschreven voor de custom functie (indien nodig of gewenst).

[lucabcd]

E.Desart:

bedankt dat je me alweer probeert te helpen, ik denk echter dat ik het verhaal niet helemaal duidelijk uitgelegd heb, daarom hieronder nog een keer:

Ik heb twee waarden (B1 en C1), van deze twee waarden wil ik de waarde die het dichtste in de buurt van de waarden uit onderstaand rijtje (A1:A7) hebben, deze moet dan afgerond worden op de desbetreffende waarde

50

100

150

250

500

750

1000

(Voorbeeld: B1 = 254 , C1 = 324, de waarde uit B1 ligt het dichts bij een waarde uit het rijtje, namelijk 250, en moet daarom als uitkomst van de formule 250 gegeven worden)

Echter zit er een voorwaarde aan, cel D1 geeft een maximale waarde aan, de uitkomst uit de formule mag niet hoger zijn als dit getal,

(B1 = 254 , C1 = 324 , D1 = 230, het getal wat het dichts bij een waarde uit het rijtje ligt is 254 wat dus afgerond 250 wordt,

echter mag de uitkomst niet meer zijn als 230, dus zal de uitkomst na beneden moeten worden afgerond naar het vorige getal uit het rijtje, namelijk 150, dit zou dus de uitkomst moeten zijn van de formule)

hoop het zo iets beter uitgelegd te hebben en dat het mogelijk is dit in een formule te plaatsen

[lucabcd]

misschien is de formule echter snel op te lossen,

ik bedoelde met de waarde uit D1 een waarde die niet hoeft afgeleid te zijn uit het rijtje (bijvoorbeeld 230)

jij bedoelde met de waarde uit D1 een waarde die wel is afgeleid uit het rijtje (bijvoorbeeld 150 of 250 of 500)

ik denk daarom dat als de formule in cel D1 aangepast word en het getal hier al wordt afgerond naar beneden, de formule die jij eerder gaf uitstekend werkt....?

[E.Desart]

Probeer deze eens: Dit was deze voor mijn laatste aanpassing gisteren.

Kijk eens of deze nog een fout resultaat kan geven volgens jou:

{=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))))}

Indien zo heb ik gisteren een verbetering aangebracht die eigenlijk een verslechtering was (overbodig was), en was ik voorheen wel juist.

Als je toch nog een fout zou vinden geef mij dan de gebruikte invoerwaarden en het resultaat dat ik zou moeten bekomen.

[lucabcd]

De formule werkt prima,

hardstikke bedankt, ga mijn tooltje deze week testen en laat nog wel weten hoe dat gegaan is...

in ieder geval bedankt E.Desart voor alle tijd en moeite die je hierin gestoken hebt....

mvg luc

[lucabcd]

E.Desart,

uit de testresultaten blijkt dat er nog aan een voorwaarde voldaan moet worden, naast de voorwaarde dat de uitkomst niet hoger mag zijn als de waarde uit cel D1, mag de uitkomst ook niet hoger zijn dan de waarde uit cel E1,

er zijn dus twee voorwaarden aan de uitkomst van de formule, deze uitkomst mag niet hoger zijn als de waarden uit D1 en ook niet als de waarde uit E1

deze waarden staan apart van elkaar en niet in oplopende volgorde, voor E1 geldt hetzelfde als D1,

hoop dat dit nog in te voegen is...

gr luc

[E.Desart]

Sorry, ik had jouw vraag niet vroeger opgemerkt.

Dit is dus een bijkomende variabele. Ik interpreteer dit als dat de uitkomst niet hoger mag zijn dan het minimum van D1 en E1? Waar ik E1 als dezelfde manier te behandelen beschouw als D1?

Werkt dit zoals bedoeld?

{=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,MIN(D1,E1)),A1:A7))

))}

[lucabcd]

en hij werkt weer,

hardstikke bedankt nogmaals!!!

gr luc