Excel Afronden naar waarde uit tabel

[Zwegertje]

Beste Tweakers,

Ik weet niet of ik mijn vraag op de juiste plek stel, zo niet laat ik me graag verwijzen.
Maar hier komt ie:

Mijn vraag
Ik zou graag een random waarde (O13) omhoog laten afronden naar een door mij gemaakte reeks.
Klinkt nogal vaag dus hier een voorbeeld:
Reeks:
10
25
50
100
150
250
De reeks staat in een kolom in Excel.
Als ik dan 9 invul, zou ik graag zien dat hij naar 10 afrond. Als ik 20 invul moet hij naar 25 afronden, en 101 moet naar 150 afronden.

Wat ik al gevonden of geprobeerd heb
Ik heb een combinatie van de functie ALS en AFRONDEN.BOVEN geprobeerd te gebruiken.
Met voor de eerste cijfers ALS O13<10 dan 10, en ALS X<25 dan 25. Daarna een AFRONDEN.BOVEN functie die afrond op 50 tallen. (ALS O13>49) Helaas was de functie zo ingewikkeld dat ik de fouten er niet uitkreeg en hij het dus niet deed.

Mocht er een simpelere oplossing zijn hoor ik het ook graag!

[Icephase]

Nogal wiedes dat dit een heel ingewikkelde formule wordt... dit is ook geen afronden meer te noemen natuurlijk.

Je zult toch iets met geneste ALS-functies gaan doen denk ik. Voor het gemak -of om de formule te analyseren- zou je het in stukjes kunnen opknippen.

[naitsoezn]

Ik heb laatst iets soortgelijks opgelost door een combinatie van MATCH(,,1) en OFFSET. Met MATCH kun je een waarde opzoeken in een rij (je krijgt dan de relatieve positie van de opzoekwaarde terug), en met OFFSET kun je die bijbehorende waarde uit de rij opzoeken.

Als je in A1:A7 bijvoorbeeld de reeks hebt staan waarnaar je wilt afronden (10, 25, 50, 100, 150, 200, met een nul ervoor):
0
10
25
50
100
150
200
En in B1 staat je getal die je omhoog wilt afronden, dan werkt de volgende formule:
OFFSET(A1, MATCH(B1,A1:A7,1), 0)

@Hieronder: Bijna een vert.zoeken, maar dan net even anders

[ Voor 39% gewijzigd door naitsoezn op 23-08-2016 15:37 ]

[Tazzios]

Ik heb net vert.zoeken met benaderen geprobeerd echter toont hij bij 11 dan 10, net de verkeerde kant helaas.

Ik zou een zoek/mapping tabel maken:

Waarde tonen
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10
9 10
10 10
11 25
12 25
13 25
14 25
15 25
etc

formule die je dan nodig heb t om uit te lezen: =VERT.ZOEKEN([Waarde];[mappingtabel];2;0)

Veel antwoorden om 15:29

@ naitsoezn Je hebt dus eigenlijk de vert.zoeken functie zelf gebouwd.

[ Voor 17% gewijzigd door Tazzios op 23-08-2016 15:34 ]

[Zwegertje]

Het is inmiddels gelukt, heb de fouten er uit gekregen.
=ALS(O13<=10;10;ALS(O13<25;AFRONDEN.BOVEN(O13;25);AFRONDEN.BOVEN(O13;50)))

De Verticaal zoeken optie was wellicht een betere oplossing geweest.

Edit:
Zo dus
=ALS(O13>150;250;ALS(O13<=10;10;ALS(O13<25;AFRONDEN.BOVEN(O13;25);AFRONDEN.BOVEN(O13;50))))

[ Voor 43% gewijzigd door Zwegertje op 23-08-2016 15:36 ]

[CeesKees]

Dit zijn ze allemaal?

Maak ergens anders een tabel met alle (individuele ) getallen van 0 tot 200, zet in de tweede kolom het getal van de waarde die het moet hebben en laat er een verticaal zoeken (of sommen.als) formule op los.


_{Edit: Wat tazzios zegt dus.}

[ Voor 8% gewijzigd door CeesKees op 23-08-2016 15:30 ]

[Eppo ©]

Wat ook mogelijk is, is het volgende:

Maak een lijst met je waardes gesorteerd van A-Z en van Z-A. Zoals dit:
10 250
25 150
50 100
100 50
150 25
250 10

En daarna kan je je de volgende formule gebruiken (D1 is de waarde die ik invul):

=ALS((D1-INDEX(A1:A6;VERGELIJKEN(D1;A1:A6;1)))<(INDEX(B1:B6;VERGELIJKEN(D1;B1:B6;-1))-D1);INDEX(A1:A6;VERGELIJKEN(D1;A1:A6;1));INDEX(B1:B6;VERGELIJKEN(D1;B1:B6;-1)))

Wat hij doet is eerst het dichtstbijzijnde getal opzoeken (zowel de vorige als de eerst komende). Van het gevonden getal haalt hij het getal dat ik invulde af. Het kleinste getal bepaald dan welke formule we gaan gebruiken. Het gaat alleen fout als je een waarde zoekt onder het kleinste getal (dus in dit geval onder de 10). Je kan dit oplossen door kleiner getal te pakken dan je ooit gaat gebruiken, bijv -10. Je krijgt dan de volgende tabel:

-10 250
10 150
25 100
50 50
100 25
150 10
250 -10

[Paultje3181]

Je kunt natuurlijk ook een geneste als maken die controleert of veld-O13 negatief is.

Visual Basic:

=Als(A1-O13>0;A1;Als(A2-O13>0;A2;als(A3-O13>0;A3;als(A4-O13>0;A4;als(A5-O13>0;A5;als(A6-O13>0;A6;"Grootste waarde"))))))

Niet geteste code, maar klopt wel volgens mij.

Edit: Code werkt, je reeks staat in A1:A6. Je waarde in O13. Hoop alleen dat je reeks niet te groot is...

[ Voor 16% gewijzigd door Paultje3181 op 17-09-2016 15:36 ]

[Anoniem: 1248024]

Of in 1 formule:

=ALS(B1=VERT.ZOEKEN(B1;A1:A7;1);B1;ZOEKEN(VERT.ZOEKEN(B1;A1:A7;1);A1:A7;A2:A7))

Waarbij van A2 tem A7 de reeks staat met een -1 aan het begin! en B1 de te zoeken waarde is

	A	B
1	-1	0
2	10
3	25
4	50
5	100
6	150
7	250

[F_J_K]

Welkom @Anoniem: 1248024! Dank voor het meedenken, maar dat lijkt me niet zo nodig als het topic al 3 jaar is weggezakt (Ook niet als je het topic vond toen je zelf zocht naar antwoord en een aanvulling hebt).

Dat gezegd hebbend: mooie oplossing