PDA

View Full Version : Problem solved? HLOOKUP/VLOOKUP question.



Aino
10-13-2014, 06:29 AM
Hi all,

I would like to know if I have corrected my problem with the HLOOKUP and VLOOKUP functions. Here's the original code:

'1)
‘Range of the table:
Set R3 = Sheets("Tables").Range("C28:I39")
‘HVLdown is HVL rounded down with 0.05 resolution:
HVLdown = Application.WorksheetFunction.RoundDown(2 * HVL, 1) / 2
‘Finding the HVLdown from the table:
Application.HLookup(HVLdown, R3, 2)

This didn't always work (most of the time it did, though), apparently because HVLdown was not an exact number and sometimes the HLOOKUP function returned an index that was one index smaller than the index of HVLdown. Here's how the code is now:

'2)
‘Range of the table:
Set R3 = Sheets("Tables").Range("C28:I39")
‘HVLdown is HVL rounded down with 0.05 resolution:
HVLdown = Application.WorksheetFunction.RoundDown(2 * HVL, 1) / 2
'I'm hoping this is exact enough:
HVLdown = Application.WorksheetFunction.Round(HVLdown, 2)
‘Finding the HVLdown from the table:
Application.HLookup(HVLdown, R3, 2)

The code seems to work now, but since I can't check every HVL number, I'm not sure. Will this correct the problem? Here is my third version, that I'm thinking of implementing:

'3)
‘Range of the table:
Set R3 = Sheets("Tables").Range("C28:I39")
‘HVLdown is HVL rounded down with 0.05 resolution:
HVLdown = Application.WorksheetFunction.RoundDown(2 * HVL, 1) / 2
'Increasing HVLdown by an epsilon:
HVLdown = HVLdown + 0.0001
‘Finding the HVLdown from the table:
Application.HLookup(HVLdown, R3, 2)

Since the HLOOKUP finds the biggest number that is smaller than HVLdown, should this work for sure? Is this better than the code number 2)? This code is actually quite important to many people and it needs to be robust.

Thank you in advance!

-Aino

SamT
10-13-2014, 07:18 AM
Aino,

Welcome to VBA Express. I hope your visits here are fruitful.

Well, if "epsilon" means "a number less than or equal to the smallest difference between any two numbers in a table," then yes, that will work.
Sorry, I am a coder, not a mathemagician, so I am not familiar with "epsilon."

One would have to analyze the contents of the R3 to answer the question. I doubt that it is possible to have no errors ever.

But, it is just code: LookUp HVLDown - 0.0001, HVLDown, and HVLDown + 0.0001 and use the two that match. If all three match, then you are too small. Use HVLDown, HVLDown + 0.0001, and HVLDown + 0.0002.

You can use a loop, with smaller and smaller additives until HVLDown + n and HVLDown + n + 0.000...1 return the same value to get as exact an answer as you want.

Don't leave just yet, I'll bet there is someone here who will give you a better solution than that.

Aino
10-13-2014, 07:45 AM
Thank you for your quick reply, and thank you for welcoming me to the forum. :)

First, let's assume that R3 is correct. The table resolution is 0.05 (the values are 0.30, 0.35, 0.4 ... 0.55, 0.60), so here "epsilon" or "small number" is indeed smaller than the resolution or "difference between any two numbers in a table". I'm also hoping that 0.0001 is bigger than the computer accuracy (for example, I hope that 0.7/2+0.0001>0.35)... Let's also assume that the values smaller than 0.30 and larger than 0.60 have been dealt with. The idea in the code 3) was to make the RTVdown just a little bit bigger (e.g. 0.3501) than the desired number in the table (e.g. 0.35) so that that the desired number (e.g. 0.35) would be chosen by the HLOOKUP. Did I understand correctly that you would give your vote to code 3)?

The loop with smaller and smaller additives sounds interesting, but also a bit too complicated for me (I am very new at VBA) that I would probably make another coding error there somewhere..

SamT
10-13-2014, 08:10 AM
Numerical accuracy is 1x10-37

Are you sure that the actual value of R3 is 0 or .05? The displayed value is not always the stored value. Select the column, right click it, and Format Cells >> Numbers , then adjust the displayed decimal places to about 6. If things are as they seem, Ctrl+Z should restore the original formatting.

If, in fact the actual values are multiples of 0.05, then merely adding 0.0499... will always give the correct result.