software

when using vlookup referencing a value between 71% to 79% in a chart using
roundup function will not work properly.

Glennrbt wrote...
gt;when using vlookup referencing a value between 71% to 79% in a chart using
gt;roundup function will not work properly.

What's your *EXACT* formula?

quot;Harlan Grovequot; wrote:

gt; Glennrbt wrote...
gt; gt;when using vlookup referencing a value between 71% to 79% in a chart using
gt; gt;roundup function will not work properly.
gt;
gt; What's your *EXACT* formula?
gt;
gt; 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.

Glennrbt wrote...
....
gt;gt;Say that in cell A1 I enter a value. In cell A4 I will round down that value
gt;gt;(=ROUNDDOWN(A1,1)). In cell A5 I will roundup the value (=ROUNDUP(A1,1)).
gt;gt;In cell B4 I have (=VLOOKUP(A4,CHART,B2)), In cell B5 I have
gt;gt;(=VLOOKUP(A5,CHART,B2)). This works great till I have a value in cell A1
gt;gt;between 71% to 79%. If I enter the value in cell A5 manually and over ride the
gt;gt;roundup function, VLOOKUP finds the proper value.

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)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, quot;chartquot; 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/Perfora...wallDesign.pdf

I tried to use quot;=VLOOKUP(A5 1E12,CHART,B2)quot; did not work on all percentages.
How ever I played around with it and changed your formula to
quot;=VLOOKUP(A5 (1E-12),CHART,B2)quot;. This seams to be working. I can only hope
that some day there is a fix for this quot;bugquot; 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