Excel 2013 time calculation vLookup glitch

[Queseimo919]

I am working on a Tracking sheet in Excel 2013 that assigns points based on the number of hours a customer uses. Most of my customers have an appointment time of 9 am to 1 pm, which equals 4 hours. It is important that I track the exact start and stop time of each appointment. I have formatted my "Start" column as Time "1:30 PM", but it defaults back to Time "*1:30:00 PM". My "Total" column has the following formula:=SUM(I5-H5)*24 to calculate the total time hours. I then have a table called "Points" that lists the number of points a customer should earn based on the total hours of their appointment, i.e. 2 hours= 1 pt, 2.25 hours= 1.125 pts, 2.5 hours=1.25 pts, and so forth and so on until 100 hours= 50 pts. I am using the following vLookup formula to get my points earned data:=IFERROR(VLOOKUP(J:J,Points,2),0) As I was imputing my data I kept noticing that where each time was from 9 am to 1 pm my vLookup is returning 1.875 which is the row above 4hours= 2pts. I thought that maybe it was because of a blank space,etc. so I tried open multiple workbooks and retyping all of the information over, but I ended up with the same result.

I even tried using the number equivalent for the times where 9 am is 0.375 and 1 pm is 0.541666666666667, which gave me the answer 4 but still returned the row above 4 hours on my Points table.

I then figured out that if I add the following seconds 9:00:01 am and 1:00:02 pm, I still get the answer 4 but it now returns the correct 2 pt value. While I understand Excel, it will be troublesome explaining to my employees that they have to add seconds to the 9-1 times for every customer.

Please advise.....

 

[Queseimo919]

After a bit of digging, I was able to find a solution!!!!! Yayyy Nevertheless, I hope my post helps someone. I used the Method 1 from the following Support Article

http://support.microsoft.com/default...b;en-us;Q48606

and it worked!

 

[steve the fish]

Not sure but could be a floating point rounding error. Try using adding a ROUND to your formula. I would have expected an error with the lookup then though.

 

[Jake Blackmore]

Hello,

You can set two types of vlookups - if you notice when you are writing the vlookup you have a TRUE or FALSE statement at the end this changes the way in which Vlookup works.
=IFERROR(VLOOKUP(J:J,Points,2,FALSE),0) - is probably the most popular but very slow because it goes through every row in order starting from the top and working down until it gets an exact match, otherwise it retursn #N/A
=IFERROR(VLOOKUP(J:J,Points,2,TRUE),0) - is the correct one to use and is by far a faster and smarter way to work with one exception - your lookup criteria HAS to be in sort order. It's smarter because it's like looking in a phone book for a persons name - you know roughly where to go - either the 1st half for Surnames A - M or the 2nd half N to Z.
With this example if I was looking for Blackmore (my Surname) the 1st half of data would be selected and the 2nd half discarded.
Excel would repeat the process again (1st haf A-G, 2nd half H-M) again my surname falls in the 1st half of the data so Excel would continue to work with this and discard the second half of this vlookup table.
You can see that in just a few steps it would find B and a few more would find Blackmore OR the highest possible solution that isn't above Blackmore.
By that I mean if there wasn't a Blackmore but there was a BlackmorD it would return that instead as that is as close as excel can get without going above Blackmore.
This is really usefull to learn so please let me know if anything is unclear and I will do my best to explain futher.

=IFERROR(VLOOKUP(J:J,Points,2),0) - not including ANY criteria defaults this to TRUE
=IFERROR(VLOOKUP(J:J,Points,2,),0) - however this extra comma would recognise use of the criteria and the blank/no entry would force a FALSE to be used.

Also - why are you using a vlookup? Wouldn't it be easier to just multiply the hours by 0.5?

 

[Jake Blackmore]

Oh and with regards to the format of the time - you can custom the time by right clicking and format cells...

Select Custom at the bottom and use something like h:mm AM/PM