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

[lucabcd]

Hallo,

Ik heb een probleem met een formule in Excel:

In Excel heb ik een waarde dat afgerond moet worden naar het dichtsbijzijnde getal in een tabel.

bijvoorbeeld:

ik heb als uitkomst van een som 134, dit getal moet afgerond worden naar de dichtsbijzijnde waarde uit de volgende lijst:

50

100

150

250

500

750

1000

Weet iemand wat voor een formule ik hiervoor kan gebruiken?

ik heb vanalles geprobeerd maar ben er nog niet achter gekomen hoe dit nu moet?

kan iemand me hierbij helpen?

alvast bedankt voor je reactie!

mvg Luc

[E.Desart]

ik heb als uitkomst van een som 134, dit getal moet afgerond worden naar de dichtsbijzijnde waarde uit de volgende lijst

50

100

150

250

500

750

1000

Moet het getal 125 het getal 100 geven of 150?

[Rogier]

Iets zegt me dat dit makkelijker moet kunnen :eusa_whistle: maar als in de kolom A1 t/m A7 de getallen staan die je noemt, en in B1 staat het getal wat je wilt afronden, dan geeft

Code: Selecteer alles

=IF(B1<=A1;A1;IF(B1>=A7;A7;IF(ABS(B1-INDEX(A1:A7;MATCH(B1;A1:A7)))<ABS(B1-INDEX(A1:A7;MATCH(B1;A1:A7)+1));INDEX(A1:A7;MATCH(B1;A1:A7));INDEX(A1:A7;MATCH(B1;A1:A7)+1))))

volgens mij het gewenste resultaat.

[E.Desart]

Rogier,

Jij zit nog met hetzelfde dat ik zat: probeer maar even de getallen 125 en 200.

Jouw formule rond naar het bovenliggende getal af.

Deze formule had ik, met dezelfde beperking, vandaar mijn vorige vraag:

=INDEX(A1:A7,MATCH(B1+MIN(ABS(A1:A7-B1)),A1:A7))

Maar enteren als een Array formule (Cntr+shift+enter) ziet het er zo uit in formula bar:

{=INDEX(A1:A7,MATCH(B1+MIN(ABS(A1:A7-B1)),A1:A7))}

Je typt die accolades niet maar verschijnen automatisch als je de formule juist entert.

Het leren werken met array formules is het gebruiken van een grote kracht in Excel.

Nederlands:

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

[lucabcd]

oke, um na boven afronden vind ik okee... zal het even nagaan bij me baas,

echter de eerste formule werkte niet bij mij,

zal de volgende formule eens proberen:

=INDEX(A1:A7,MATCH(B1+MIN(ABS(A1:A7-B1)),A1:A7))

[lucabcd]

um bovenstaande formule werkt bij mij ook niet...

lukt het bij jullie wel zo of doe ik iets fout...

de engelse formule geeft: #NAAM?

de nederlandse formule geeft: #WAARDE!

[E.Desart]

Mijn formule werkt.

Bij het copieren van formules moet je niet alleen naar de taal zien maar ook naar de "lijst separator".

Nederland en Europees zal je ; punt-komma tussen de argumenten vinden. Amerikaans/Engels is komma de standaard separator.

Rogier kan mijn formule niet gebruiken zonder aanpassing van die separator EN omgekeerd.

Rogier en Ger gebruiken hier een mix (voor zover ik reeds zag of dacht te zien). Engelstalige Excel met niet-typisch bijbehorende separator.

Dit is afhankelijk van je instellingen in Windows.

En lees wat ik schrijf over het invoeren van array formules of je krijgt gewoon een foutmelding. Je kan mijn formule niet gewoon invoeren met Enter zonder een fout te krijgen.

[Rogier]

Mijne is overigens gemaakt met (en werkt in) OpenOffice, die is wel volledig compatible met Excell.

Maar inderdaad, die taal-specifieke functienamen en sparators... Welke Microsoft-flapdrol verzint het nou in godsnaam om dat soort technische meuk taal-afhankelijk (en dus niet uitwisselbaar) te maken :eusa_whistle:

[jadatis]

Ik heb het bij een ander topic over vollume en pijlhoogte van een ton vloeistof andersom benaderd, en heb naast de lijst een als formule gemaakt.

dan in cel J1 "als( L1< $A$1; "";L1). dat copieren naar de cellen daaronder in de lijst.

Als laatste in bijvoorbeeld A3 "MIN(J1: J100) .

Dus als het op de andere manier niet wil lukken, dan kun je dit eens proberen.

[lucabcd]

Iedereen bedankt voor jullie reactie,

E.Desart, de formule werkt inderdaad als 'array formule', moest alleen even opzoeken hoe dat werkte...

nog een vraagje:

als ik de tabel nu uit wil uitbereiden met 2500, 5000 en 10000 en deze getallen er onderaan in de tabel zbijzet, kan ik de formule dan aanpassen en van A7, A10 maken?

[lucabcd]

laat maar, beetje domme vraag en heb het al gevonden,

nogmaals bedankt voor de reacties!!

gr Luc

[E.Desart]

Iedereen bedankt voor jullie reactie,

Graag gedaan,

nog een vraagje:

als ik de tabel nu uit wil uitbreiden met 2500, 5000 en 10000 en deze getallen er onderaan in de tabel zbijzet, kan ik de formule dan aanpassen en van A7, A10 maken?

Je kan die lijst (stijgende getallen) zo lang of uitgebreid maken als je wenst en gewoon zorgen dat je referentie klopt (in jouw voorbeeld dus A7 wordt A10).

Als zich een getal zou kunnen voordoen (alleen dan) dat < (kleiner is) dan het kleinste getal van de lijst kan je de formule verfijnen als:

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

Na enteren als array wordt dit dan:

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

Als een getal groter is dan het grootste in de lijst kan geen kwaad (formule blijft kloppen).

EDIT: Dit berichtje gecrossed met jouw laatste.

PS: @ Rogier: Dat probleem met die separators doet zich alleen voor bij manueel enteren. Wanneer wij files zouden uitwisselen passen die zich automatisch aan aan de locale instellingen juist zoals de functies taal zich aanpast (tenminste als dit in Open Office hetzelfde werkt als in Windows).

[lucabcd]

Hallo, heb nog een vraagje nadat ik de succesvolle formule heb gebruikt:

is het, bij hetzelfde vraagstuk, ook mogelijk om in plaats van 1 getal af te ronden naar de waarden uit een tabel, dit met twee waarden te doen?

Voorbeeld:

heb twee getallen 318 en 508 en heb weer dezelfde lijst:

50

100

150

250

500

1000

enz...

is het nu mogelijk om uit de twee getallen het getal af te ronden dat het dichtste bij een waarde uit de lijst zit, het getal dat het dichts bij een waarde zit is 508 wat afgerond moet worden na 500..?

hoop weer op reacties en dat het mogelijk is

alvast bedankt voor de moeite!

groeten Luc

[E.Desart]

Ik begrijp de vraag niet juist (of ben niet zeker).

Kan je eens wiskundig duidelijk maken waarom en hoe de waarden '318 en 508' op 500 komen?

Zoek je gewoon het getal met het kleinste verschil naar de tabelwaarden en gebruikt dit verder als basis?

[E.Desart]

Weer als array invoeren. In C1 staat nu je 2de getal

{=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.

En in zover er geen misverstand is in hoe ik je uitleg begrijp.