Converting Hours To Decimal-VLOOKUP Problem

G Marshall

Board Regular
Joined
Dec 31, 2002
Messages
134
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

bryhamm

Board Regular
Joined
Jun 3, 2004
Messages
115
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

Board Regular
Joined
Dec 31, 2002
Messages
134
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,645
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top