Springen naar inhoud

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


  • Log in om te kunnen reageren

#1

lucabcd

    lucabcd


  • 0 - 25 berichten
  • 18 berichten
  • Gebruiker

Geplaatst op 04 november 2009 - 12:49

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

Dit forum kan gratis blijven vanwege banners als deze. Door te registeren zal de onderstaande banner overigens verdwijnen.

#2

E.Desart

    E.Desart


  • >1k berichten
  • 2391 berichten
  • VIP

Geplaatst op 04 november 2009 - 13:42

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?
Eric

#3

Rogier

    Rogier


  • >5k berichten
  • 5679 berichten
  • VIP

Geplaatst op 04 november 2009 - 13:47

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

Veranderd door Rogier, 04 november 2009 - 13:49

In theory, there's no difference between theory and practice. In practice, there is.

#4

E.Desart

    E.Desart


  • >1k berichten
  • 2391 berichten
  • VIP

Geplaatst op 04 november 2009 - 14:17

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))}
Eric

#5

lucabcd

    lucabcd


  • 0 - 25 berichten
  • 18 berichten
  • Gebruiker

Geplaatst op 04 november 2009 - 14:42

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))

Veranderd door lucabcd, 04 november 2009 - 14:45


#6

lucabcd

    lucabcd


  • 0 - 25 berichten
  • 18 berichten
  • Gebruiker

Geplaatst op 04 november 2009 - 14:48

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!

Veranderd door lucabcd, 04 november 2009 - 14:54


#7

E.Desart

    E.Desart


  • >1k berichten
  • 2391 berichten
  • VIP

Geplaatst op 04 november 2009 - 14:54

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.

Veranderd door E.Desart, 04 november 2009 - 15:03

Eric

#8

Rogier

    Rogier


  • >5k berichten
  • 5679 berichten
  • VIP

Geplaatst op 04 november 2009 - 15:37

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:
In theory, there's no difference between theory and practice. In practice, there is.

#9

jadatis

    jadatis


  • >250 berichten
  • 347 berichten
  • Ervaren gebruiker

Geplaatst op 04 november 2009 - 18:02

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.

#10

lucabcd

    lucabcd


  • 0 - 25 berichten
  • 18 berichten
  • Gebruiker

Geplaatst op 05 november 2009 - 08:22

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?

Veranderd door lucabcd, 05 november 2009 - 08:26


#11

lucabcd

    lucabcd


  • 0 - 25 berichten
  • 18 berichten
  • Gebruiker

Geplaatst op 05 november 2009 - 08:44

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

nogmaals bedankt voor de reacties!!

gr Luc

#12

E.Desart

    E.Desart


  • >1k berichten
  • 2391 berichten
  • VIP

Geplaatst op 05 november 2009 - 08:58

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).

Veranderd door E.Desart, 05 november 2009 - 09:13

Eric

#13

lucabcd

    lucabcd


  • 0 - 25 berichten
  • 18 berichten
  • Gebruiker

Geplaatst op 06 november 2009 - 12:30

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

Veranderd door lucabcd, 06 november 2009 - 12:32


#14

E.Desart

    E.Desart


  • >1k berichten
  • 2391 berichten
  • VIP

Geplaatst op 06 november 2009 - 12:35

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?

Veranderd door E.Desart, 06 november 2009 - 12:38

Eric

#15

E.Desart

    E.Desart


  • >1k berichten
  • 2391 berichten
  • VIP

Geplaatst op 06 november 2009 - 12:54

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.
Eric





0 gebruiker(s) lezen dit onderwerp

0 leden, 0 bezoekers, 0 anonieme gebruikers

Ook adverteren op onze website? Lees hier meer!

Gesponsorde vacatures

Vacatures