Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup does not work between 71% to 79% refernceing roundup numb
when using vlookup referencing a value between 71% to 79% in a chart using
roundup function will not work properly. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup does not work between 71% to 79% refernceing roundup numb
Glennrbt wrote...
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup does not work between 71% to 79% refernceing roundup n
"Harlan Grove" wrote: Glennrbt wrote... 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? 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup does not work between 71% to 79% refernceing roundup n
Glennrbt wrote...
.... 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. 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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup does not work between 71% to 79% refernceing roundup n
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/Perfora...wallDesign.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Vlookup to work with formula in reference cell | Excel Discussion (Misc queries) | |||
IF / VLOOKUP formula won't work until saved | Excel Worksheet Functions | |||
why won't vlookup work in a long list | Excel Worksheet Functions | |||
Vlookup won't work, any other ideas? | Excel Worksheet Functions | |||
VLOOKUP won't work | Excel Worksheet Functions |