Returning next value for VLOOKUP table
Posted by Debbie on May 31, 2000 7:32 AM
Hi, I hope someone can help. I have a ratio which has to be looked up on a separate range table. The problem is that Excel takes the value that is less than, for example if my ratio is .514444, the lookup table returns the value for ratio .514(the whole table is to 3 decimals), which is size factor 1.572. Our computer system takes the next value, in other words, once the ratio is over .514, for example,.5141, it would take the value for .515, which is size factor 1.570. I have built a spreadsheet which will simulate the computer's calculations so that we have a 'preview' of the calculated value, but can't think of hwo to get around this. Please Help!! :-)
Posted by Jill-ann on May 31, 2000 9:18 AM
If I understand you correctly, you want it to round up when it performs it's lookup. Assuming the number of decimals is consistent (5 in your example) you would do this:
=vlookup(roundup(ratio,3),rangetable,#)
Hope that helps!
Posted by Debbie on May 31, 2000 2:27 PM
It is not really rounding up, but once it is over a certain value, for instance .1251, it would not use the size factor for .125, but for .126 which is the next entry in the table, so it wouldn't be a normal round, which is the trouble i am having. Thanks for the input, though, Jill-ann
