[MS Excel 2013] formule vindt gegevens niet

[nvanthoff]

Ik heb een Z-waarde tabel gemaakt waarin ik een formule wil laten zoeken. De cel die gezocht moet worden in de Z-waarde tabel is "C36" van een ander tabblad. Het idee is dus, dat wanneer ik een uitkomst krijg uit een berekening, dat ik daaronder gelijk zie welke waarde uit de Z-waarde tabel daarbij hoort. Dus ik had de volgende twee dingen geprobeerd:

Omdat de waarde in "C36" twee decimalen heeft (#,##) en de Z-waarde tabel verticaal zoekt op één decimaal (#,##) en horizontaal de tweede decimaal zoekt (#,##) zit er een afrondingformule, in de formule. Als test heb ik in "C36" het getal "1,63" geschreven.

=INDEX('Z-tabel'!B2:K62;MATCH((ROUND($C$36;1));'Z-tabel'!A2:A62;0);MATCH($C$36-(ROUND($C$36;1));'Z-tabel'!B1:K1;0))

en

=VLOOKUP((ROUND($C$36;1));'Z-tabel'!A2:A62;MATCH($C$36-(ROUND($C$36;1));'Z-tabel'!B2:K2;0);FALSE)

Maar via de "INDEX" krijg ik #N/A terug en via de tweede "VLOOKUP" krijg ik ook #N/A terug. Ik denk zelf dat de fout in de "MATCH" formule zit. Want zelfs als laat ik de formule letterlijk zoeken naar de cel die gevonden moet worden, dan krijg ik nog steeds #N/A, zie onderstaande afbeelding:



Als ik de formule niet laat eindige op een "0", wat dus een exacte match zou moeten vinden, maar op "1", dan krijg ik we een rij terug, uiteraard de verkeerde, dat wel. Maar het is gek, want de formule zou een exacte rij moeten kunnen vinden, omdat 1,6 erin staat.

Ik heb al gecontroleerd of alle cellen via celeigenschappen op "numeriek" staan en dat is het geval.

De Z-waarde tabel ziet er zo uit:


Iemand een idee wat er mis gaat?

[mrtnptrs]

Sorry, maar het plaatje is zo klein, dat het niet te lezen valt. Heb je ook al even op google gezocht voor een oplossing?

[ Voor 30% gewijzigd door mrtnptrs op 02-07-2013 20:03 ]

[nvanthoff]

Humz, vreemd, heb de afbeeldingen aangepast zodat ze leesbaar zijn.

En ik had al flink gegoogled, zo was ik op die formules uitgekomen die ik gebruikt heb.

[Sjeik]

Je array bevat maar 1 cel (A18), dit zou volgens mij een range moeten zijn... dus met een : erin.

[F_J_K]

Excel is geen OS dus Windows Clients ==> CSA.

Is het datatype van Z!B2:K62 gelijk dat van C36? (Vraag ik voor de zekerheid, ik weet niet of je dat onder de definitie van alle zag staan).

Edit: je hebt het over 'numeriek', dus Nederlands. Ik neem aan dat je "gewoon" Number bedoelt

[ Voor 45% gewijzigd door F_J_K op 02-07-2013 20:21 ]

[nvanthoff]

Sjeik schreef op dinsdag 02 juli 2013 @ 20:14:
Je array bevat maar 1 cel (A18), dit zou volgens mij een range moeten zijn... dus met een : erin.

Ja, klopt. Dat was ook alleen om te laten zien, dat wanneer je de formule laat zoeken in de cel die de exacte waarde bevat, dat die hem dan nog steeds niet "vindt". De arrey die ik gebruik ik de complete formule is 'Z-tabel'!B2:K62

F_J_K schreef op dinsdag 02 juli 2013 @ 20:18:
Edit: je hebt het over 'numeriek', dus Nederlands. Ik neem aan dat je "gewoon" Number bedoelt

haha Ja, ik bedoelde "nummer" met numeriek . Maar beide zijn dat, zowel de Z-waarden als C36.

[pedorus]

De juiste formule lijkt me =NORMSDIST(-$C$36). Alternatief moet je maar eens kijken hoe ROUND, MATCH en VLOOKUP nu echt werken.

Deelstapjes gebruiken of de functie "Evaluate formula" wil ook wel eens helpen.

[ Voor 25% gewijzigd door pedorus op 02-07-2013 20:36 ]

[nvanthoff]

Ik denk dat ik wat gevonden heb waarom het mis gaat met de Match-formule. Ik gebruik "Round" maar als ik wil laten zoeken naar 1,45, krijg ik dus via Round en 1,45-Round de getallen 1,50 en 0,05. Waardoor de formule dus zoekt naar 1,55 in de tabel :-/ Ik ga met die wijsheid even verder knutselen.
Dus ik gebruik nu maar even "ROUNDDOWN". Zodat 1,45 wordt afgerond naar 1,40...

[ Voor 12% gewijzigd door nvanthoff op 02-07-2013 20:33 ]

[nvanthoff]

Goed, met de formule

=MATCH(ROUNDDOWN($C$36;1);'Z-tabel'!A:A;0)

lijkt nu in ieder geval de juiste verticale zoektocht goed te komen. Welk getal ik ook invoer bij "C36", hij geeft altijd de juiste kolom.

Maar het verticaal aanwijzen van de correcte rij blijkt maar niet goed te gaan. Formule nu is

=MATCH($C$36-(ROUNDDOWN($C$36;1));'Z-tabel'!1:1;0)

Gek genoeg, als ik deze wijzig naar

=MATCH($C$36-(ROUNDDOWN($C$36;1));'Z-tabel'!1:1;1)

(laatste "1" is "less than", waar "0" staat voor ëxact")
Dan geeft die wel netjes de rij die voor de waarde staat die ik zoek. Het blijkt dat de formule aan te passen naar:
=MATCH($C$36-(ROUNDDOWN($C$36;1));'Z-tabel'!1:1;1)+1

Ook niet het gewenste effect heeft. Want er komt dan niet altijd de correcte cel uit...

Dus nu alleen nog even het horizontaal selecteren goed krijgen en dan ben ik er. De formule is momenteel:

=INDEX('Z-tabel'!$B$2:$K$62;MATCH(ROUNDDOWN($C$36;1);'Z-tabel'!$A:$A;0);(MATCH($C$36-(ROUNDDOWN($C$36;1));'Z-tabel'!$1:$1;0)))

Waarbij ik dus #N/A krijg, tenzij ik de laatste "0" in een "1" verander, maar dan krijg ik dus niet de correcte cel.

[pedorus]

Is dit huiswerk ofzo? De juiste kolom lijkt mij MATCH(ROUND(ROUND($C$36;2)-ROUNDDOWN(ROUND($C$36;2);1);2);'Z-tabel'!1:1;0)

Met wat kennis van floating point arithmetic valt dit weer terug te brengen tot 2 functieaanroepen, nml MATCH+MOD (of ROUND, maar dat is minder logisch), waarbij je een match type 1 uitvoert. Succes