N/A results on vlookup with time calculations

[Freida]

My worksheet calculates my timesheet. Each day I enter the time in and the
time out. The difference gets rounded to the nearest quarter of an hour each
day.
(I later add up all the days and do all sorts of other calcs...not important
to this question).

I figure out the rounding by using vlookup with the following formula:
=IF(D9-C9>0,INT((D9-C9)*24)+VLOOKUP(((D9-C9)*24-INT((D9-C9)*24)),$B$77:$C$81,2),0)

here is the data in b77:c81
0 0
0.13 0.25
0.38 0.5
0.63 0.75
0.88 1

This has been working fine for 2 months now. But, today my time in was
9:00AM and my timeout was 1:00PM and the result was N/A !!!! It works for
9:00 to 1:01 and for 9:00 to 12:00 and for any other combo in the past 2
months.

When I tried stepping throught the formula, it came up to a step doing 4-4
and then came up with the following result to that calc:
-8.88178419700125E-16 !!

I am totally perplexed... Will appreciate help on this one.
I am pretty much a self-taught excel novice...please take that into
consideration when responding. Thanks

 

[Niek Otten]

Look here to learn what happened
--
Kind regards,

Niek Otten
Microsoft MVP - Excel
and how to cure this:

http://support.microsoft.com/kb/78113


| My worksheet calculates my timesheet. Each day I enter the time in and the
| time out. The difference gets rounded to the nearest quarter of an hour each
| day.
| (I later add up all the days and do all sorts of other calcs...not important
| to this question).
|
| I figure out the rounding by using vlookup with the following formula:
| =IF(D9-C9>0,INT((D9-C9)*24)+VLOOKUP(((D9-C9)*24-INT((D9-C9)*24)),$B$77:$C$81,2),0)
|
| here is the data in b77:c81
| 0 0
| 0.13 0.25
| 0.38 0.5
| 0.63 0.75
| 0.88 1
|
| This has been working fine for 2 months now. But, today my time in was
| 9:00AM and my timeout was 1:00PM and the result was N/A !!!! It works for
| 9:00 to 1:01 and for 9:00 to 12:00 and for any other combo in the past 2
| months.
|
| When I tried stepping throught the formula, it came up to a step doing 4-4
| and then came up with the following result to that calc:
| -8.88178419700125E-16 !!
|
| I am totally perplexed... Will appreciate help on this one.
| I am pretty much a self-taught excel novice...please take that into
| consideration when responding. Thanks
|
|
|
|

 

[JE McGimpsey]

To round time, since XL stores times as fractional days and there are 96
quarter hours per day, one way

=ROUND((D9-C9)*96,0)/96

Format as time.

Your VLOOKUP() problem is due to small rounding errors inherent in the
way XL and most other spreadsheets store and calculate data. See

http://cpearson.com/excel/rounding.htm

for more

Format as In article

 

[Freida]

Thank you. If I understood all this correctly I had to make my ss calcs less
precise.
I did that and now it is working.

 

[Freida]

Thank you for your response; it is good to understand how the time is stored
for future worksheet calcs.