Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
N/A results on vlookup with time calculations
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-C90,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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
N/A results on vlookup with time calculations
Look here to learn what happened
-- Kind regards, Niek Otten Microsoft MVP - Excel and how to cure this: http://support.microsoft.com/kb/78113 "Freida" wrote in message ... | 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-C90,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 | | | | |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
N/A results on vlookup with time calculations
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 wrote: 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-C90,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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
N/A results on vlookup with time calculations
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. "Niek Otten" wrote: Look here to learn what happened -- Kind regards, Niek Otten Microsoft MVP - Excel and how to cure this: http://support.microsoft.com/kb/78113 "Freida" wrote in message ... | 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-C90,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 | | | | |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
N/A results on vlookup with time calculations
Thank you for your response; it is good to understand how the time is stored
for future worksheet calcs. "JE McGimpsey" wrote: 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 wrote: 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-C90,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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time difference calculations, daylight savings time, Excel | Excel Discussion (Misc queries) | |||
Copying formulas without changing previous results/calculations | Excel Worksheet Functions | |||
Simple, yet complex problem! Using results as new data during calculations? | Excel Worksheet Functions | |||
Help on performing calculations on formula results | Excel Worksheet Functions | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions |