Converting Hours To Decimal-VLOOKUP Problem

[G Marshall]

Hello
I have a problem with a VLOOKUP formula and I would appreciate some help. I have searched through other VLOOKUP questions but I have been unable to find a satisfactory answer.

Cell A1 Format Time [h]:mm
Cell B1 Format Number 2 Decimal places and has the formula =HOUR(A1)+(MINUTE(A1)/60) This returns the time value in A1 converted to a decimal.

Cell C1 Format Number 2 Decimal places and has the VLOOKUP formula
=IF(ISNUMBER(B1),VLOOKUP(B1,{3.75,0.5;3.5,0.5;3.66,0.5},2,0),"")

In Cell A1 if I enter 3:45 or 3:30 then 0.5 appears in Cell C1 which is correct, however when I enter 3:40 in Cell A1 the #N/A appears in Cell C1.

I think the problem lies with the fact that 3:30 in time converts exactly to 3.50 in decimal and 3:45 in time converts exactly to 3.75 in decimal but 3:40 converts to 3.6666667 infinite , and is not returning an exact match of 3.66 even though I have the cell formatted to 2 decimal places.
How can I get around this problem in my VLOOKUP formula, I would appreciate some help.

Thanks


Gerald

 

[bryhamm]

You have basically pointed out your own issue. The value in B1 with your "problem" scenario is still 3.66667. Even if you format a number to 2 decimals, that does not mean that the underlying number has changed.

You could try altering your formula in C1 to:

=IF(ISNUMBER(B1),VLOOKUP(ROUND(B1,2),{3.75,0.5;3.5,0.5;3.67,0.5},2,0),"")


I added the round function and changed your lookup to search for 3.67 instead of 3.66

 

[G Marshall]

Thanks a million for that help, I have scratched my head for quite a while trying to solve the issue. You were right I did know the problem but knowing the problem and solving it are two different matters.

Thanks again


Gerald

 

[Aladin Akyurek]

The following would cover the intended scope of your VLOOKUP formula:

=IF(ISNUMBER(MATCH(ROUND(A1*24,2),{3.50,3.67,3.75},0)),0.5,0)

which substitutes MATCH for VLOOKUP (inessential) but uses ROUND to capture the intent.