G
Glennrbt
when using vlookup referencing a value between 71% to 79% in a chart using
roundup function will not work properly.
roundup function will not work properly.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
vlookup does not work between 71% to 79% refernceing roundup numb
H
Harlan Grove
Glennrbt wrote...
What's your *EXACT* formula?
when using vlookup referencing a value between 71% to 79% in a chart using
roundup function will not work properly.
What's your *EXACT* formula?
G
Glennrbt
Harlan Grove said:Glennrbt wrote...
What's your *EXACT* formula?
Say that in cell A1 I enter a value. In cell A4 I will round down that value (=ROUNDDOWN(A1,1)). In cell A5 I will roundup the value (=ROUNDUP(A1,1)). In cell B4 I have (=VLOOKUP(A4,CHART,B2)), In cell B5 I have (=VLOOKUP(A5,CHART,B2)). This works great till I have a value in cell A1 between 71% to 79%. If I enter the value in cell A5 manually and over ride the roundup function, VLOOKUP finds the proper value.
H
Harlan Grove
Glennrbt wrote...
....
Presumably CHART refers to a range spanning multiple columns.
Anyway, I was able to reproduce this error. The formula
=VLOOKUP(ROUNDUP(0.76,1),{0.75;0.8},1)
returns 0.75 rather than 0.8, but the formula
=VLOOKUP(ROUND(0.76,1),{0.75;0.8},1)
returns 0.8. Very odd because
=ROUND(0.76,1)=ROUNDUP(0.76,1)
returns TRUE. However, the formula
=VLOOKUP(ROUNDUP(0.76,1)-ROUND(0.76,1),{-1;0},1)
returns -1, so this could be called a bug in VLOOKUP. However, the root
cause is floating point rounding error. Excel usually applies a fudge
factor to handle very small differences, but it seems the first
argument to VLOOKUP (and HLOOKUP and LOOKUP) apprears to be one case in
which the fudge factor isn't applied. Back to old-fashioned floating
point techniques - depend on exact equality, figure out some small
amount that constitutes 'close enough', and add it to comparisons, so
if that value were 1E-12, try
=VLOOKUP(A5+1E12,CHART,B2)
....
Presumably CHART refers to a range spanning multiple columns.
Anyway, I was able to reproduce this error. The formula
=VLOOKUP(ROUNDUP(0.76,1),{0.75;0.8},1)
returns 0.75 rather than 0.8, but the formula
=VLOOKUP(ROUND(0.76,1),{0.75;0.8},1)
returns 0.8. Very odd because
=ROUND(0.76,1)=ROUNDUP(0.76,1)
returns TRUE. However, the formula
=VLOOKUP(ROUNDUP(0.76,1)-ROUND(0.76,1),{-1;0},1)
returns -1, so this could be called a bug in VLOOKUP. However, the root
cause is floating point rounding error. Excel usually applies a fudge
factor to handle very small differences, but it seems the first
argument to VLOOKUP (and HLOOKUP and LOOKUP) apprears to be one case in
which the fudge factor isn't applied. Back to old-fashioned floating
point techniques - depend on exact equality, figure out some small
amount that constitutes 'close enough', and add it to comparisons, so
if that value were 1E-12, try
=VLOOKUP(A5+1E12,CHART,B2)
G
Glennrbt
It's strange the this only happens between the amount of .71 to .79. All
other percents work just fine. Also the rounddown formula works fine.
Yes, "chart" does refers to multiple columns. The chart I am referencing is
on page 6 of a pdf file. That file can be found at the following web site,
Http://www.awc.org/technical/PerforatedShearwallDesign.pdf
I tried to use "=VLOOKUP(A5+1E12,CHART,B2)" did not work on all percentages.
How ever I played around with it and changed your formula to
"=VLOOKUP(A5+(1E-12),CHART,B2)". This seams to be working. I can only hope
that some day there is a fix for this "bug" cause I will forget to add this
extra formula (+(1E-12)) to a spread sheet LOOKUP function some day.
I also wont to say thanks for responding to this post. I discovered this a 3
or 4 weeks ago and have been trying very hard to find some one to give me a
fix or work around. I have been able to find a lot of Excel experts till I
point this out and I find I know more about Excel than they do, and I know
very little.
Thanks Again
other percents work just fine. Also the rounddown formula works fine.
Yes, "chart" does refers to multiple columns. The chart I am referencing is
on page 6 of a pdf file. That file can be found at the following web site,
Http://www.awc.org/technical/PerforatedShearwallDesign.pdf
I tried to use "=VLOOKUP(A5+1E12,CHART,B2)" did not work on all percentages.
How ever I played around with it and changed your formula to
"=VLOOKUP(A5+(1E-12),CHART,B2)". This seams to be working. I can only hope
that some day there is a fix for this "bug" cause I will forget to add this
extra formula (+(1E-12)) to a spread sheet LOOKUP function some day.
I also wont to say thanks for responding to this post. I discovered this a 3
or 4 weeks ago and have been trying very hard to find some one to give me a
fix or work around. I have been able to find a lot of Excel experts till I
point this out and I find I know more about Excel than they do, and I know
very little.
Thanks Again