|
The following has caused me considerable embarassment..can anyone explain?
Hi,
I recently constructed a large spreadsheet for a colleague which, amongst other things samples at intervals the number of staff available (that is not on a break, not on leave , not sick etc.) based on a staff roster. For example at 10:15, 10:30, etc how many staff are available to take calls during that interval. A very simplified version of the problem is as follows: Row 2 Column A = a list of names Column B = each member of staffs shift start time i.e. 08:00 Column C = each member of staffs shift end time i.e. 16:00 Row 1, starting at Column D is filled with each interval i.e. 08:00, 08;15, 08:30, 08:45..up to, for example, 20:00. The following formula is entered into each cell starting at row 2, column D up to the final interval, in this case 20:00, for each name - constructing a kind of truth table. =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Seems straightforward enough, I hope. This works as expected - but only up to a point! If you constuct this table and enter the following data you may encounter an unexpected problem. For the 1st staff member Column A row 2 give a start time of 08:00 and an end time of 14:45. This should show correct. The staff member is showing as "NOT IN" at the 14:45 interval. Now increase the end time for the same staff member by 00:15 intervals. At 15:00 the table still reads correctly - "NOT IN" at 15;00. At 15:15 however... If you have the inclination continue this and each time see what the table shows. Apologies for being so verbose. Does anyone have an explanation? Is this a known problem? Any solutions? Thanks in advance. Ian |
Could it be that your rows are not lined up? Your formula is currently
reading: =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Should it really be reading: =IF(AND(D$2=$B2,D$2<$C2),"IN","NOT IN") ---- Regards, John Mansfield http://www.pdbook.com "Ian" wrote: Hi, I recently constructed a large spreadsheet for a colleague which, amongst other things samples at intervals the number of staff available (that is not on a break, not on leave , not sick etc.) based on a staff roster. For example at 10:15, 10:30, etc how many staff are available to take calls during that interval. A very simplified version of the problem is as follows: Row 2 Column A = a list of names Column B = each member of staffs shift start time i.e. 08:00 Column C = each member of staffs shift end time i.e. 16:00 Row 1, starting at Column D is filled with each interval i.e. 08:00, 08;15, 08:30, 08:45..up to, for example, 20:00. The following formula is entered into each cell starting at row 2, column D up to the final interval, in this case 20:00, for each name - constructing a kind of truth table. =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Seems straightforward enough, I hope. This works as expected - but only up to a point! If you constuct this table and enter the following data you may encounter an unexpected problem. For the 1st staff member Column A row 2 give a start time of 08:00 and an end time of 14:45. This should show correct. The staff member is showing as "NOT IN" at the 14:45 interval. Now increase the end time for the same staff member by 00:15 intervals. At 15:00 the table still reads correctly - "NOT IN" at 15;00. At 15:15 however... If you have the inclination continue this and each time see what the table shows. Apologies for being so verbose. Does anyone have an explanation? Is this a known problem? Any solutions? Thanks in advance. Ian |
How did you enter the times in row 1? If they're formulas, they may be
slightly greater or less than the time that's being displayed. You could type the first two numbers, and select them Then point to the fill handle (the small black square at the bottom right of the selection) When the pointer changes to a black plus sign, drag across, to the last column Ian wrote: Hi, I recently constructed a large spreadsheet for a colleague which, amongst other things samples at intervals the number of staff available (that is not on a break, not on leave , not sick etc.) based on a staff roster. For example at 10:15, 10:30, etc how many staff are available to take calls during that interval. A very simplified version of the problem is as follows: Row 2 Column A = a list of names Column B = each member of staffs shift start time i.e. 08:00 Column C = each member of staffs shift end time i.e. 16:00 Row 1, starting at Column D is filled with each interval i.e. 08:00, 08;15, 08:30, 08:45..up to, for example, 20:00. The following formula is entered into each cell starting at row 2, column D up to the final interval, in this case 20:00, for each name - constructing a kind of truth table. =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Seems straightforward enough, I hope. This works as expected - but only up to a point! If you constuct this table and enter the following data you may encounter an unexpected problem. For the 1st staff member Column A row 2 give a start time of 08:00 and an end time of 14:45. This should show correct. The staff member is showing as "NOT IN" at the 14:45 interval. Now increase the end time for the same staff member by 00:15 intervals. At 15:00 the table still reads correctly - "NOT IN" at 15;00. At 15:15 however... If you have the inclination continue this and each time see what the table shows. Apologies for being so verbose. Does anyone have an explanation? Is this a known problem? Any solutions? Thanks in advance. Ian -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Hello John,
Thanks for looking. No. The formula is correct. And the formula works fine - up to a point! If the start time is set to 08:00 and the end time 15:15 (the interval headings start at 0800 and increase by 00:15) the results are correct until the interval 15:15!! There are further anomalies. Row 1 beginning at column D is where the interval headings for the table are located. The formula below is entered in D2. Cheers, Ian -----Original Message----- Could it be that your rows are not lined up? Your formula is currently reading: =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Should it really be reading: =IF(AND(D$2=$B2,D$2<$C2),"IN","NOT IN") ---- Regards, John Mansfield http://www.pdbook.com "Ian" wrote: Hi, I recently constructed a large spreadsheet for a colleague which, amongst other things samples at intervals the number of staff available (that is not on a break, not on leave , not sick etc.) based on a staff roster. For example at 10:15, 10:30, etc how many staff are available to take calls during that interval. A very simplified version of the problem is as follows: Row 2 Column A = a list of names Column B = each member of staffs shift start time i.e. 08:00 Column C = each member of staffs shift end time i.e. 16:00 Row 1, starting at Column D is filled with each interval i.e. 08:00, 08;15, 08:30, 08:45..up to, for example, 20:00. The following formula is entered into each cell starting at row 2, column D up to the final interval, in this case 20:00, for each name - constructing a kind of truth table. =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Seems straightforward enough, I hope. This works as expected - but only up to a point! If you constuct this table and enter the following data you may encounter an unexpected problem. For the 1st staff member Column A row 2 give a start time of 08:00 and an end time of 14:45. This should show correct. The staff member is showing as "NOT IN" at the 14:45 interval. Now increase the end time for the same staff member by 00:15 intervals. At 15:00 the table still reads correctly - "NOT IN" at 15;00. At 15:15 however... If you have the inclination continue this and each time see what the table shows. Apologies for being so verbose. Does anyone have an explanation? Is this a known problem? Any solutions? Thanks in advance. Ian . |
Hello Debra,
Thank you. You are right. In order to increase the flexibility of the sheet the interval headings reference an opening time cell and an interval cell. The first heading (D1) references the opening time cell and the other interval headings are constructed using the formulas (in E1) =d$1+interval cell, (in F1) =e$1+interval cell etc. I have been careful to format the cells with a custom format hh:mm:ss. The interval cell for this example contains 00:15:00 formatted hh:mm:ss. I want to be able to sample using a variety of intervals. You've found the source of the problem - but not the solution. How does this inaccuracy creep in? What can be done to gaurd against? Excel not to be relied on? What seems a very straightforward application of excel has caused me..grief! Thanks for your help. Ian -----Original Message----- How did you enter the times in row 1? If they're formulas, they may be slightly greater or less than the time that's being displayed. You could type the first two numbers, and select them Then point to the fill handle (the small black square at the bottom right of the selection) When the pointer changes to a black plus sign, drag across, to the last column Ian wrote: Hi, I recently constructed a large spreadsheet for a colleague which, amongst other things samples at intervals the number of staff available (that is not on a break, not on leave , not sick etc.) based on a staff roster. For example at 10:15, 10:30, etc how many staff are available to take calls during that interval. A very simplified version of the problem is as follows: Row 2 Column A = a list of names Column B = each member of staffs shift start time i.e. 08:00 Column C = each member of staffs shift end time i.e. 16:00 Row 1, starting at Column D is filled with each interval i.e. 08:00, 08;15, 08:30, 08:45..up to, for example, 20:00. The following formula is entered into each cell starting at row 2, column D up to the final interval, in this case 20:00, for each name - constructing a kind of truth table. =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Seems straightforward enough, I hope. This works as expected - but only up to a point! If you constuct this table and enter the following data you may encounter an unexpected problem. For the 1st staff member Column A row 2 give a start time of 08:00 and an end time of 14:45. This should show correct. The staff member is showing as "NOT IN" at the 14:45 interval. Now increase the end time for the same staff member by 00:15 intervals. At 15:00 the table still reads correctly - "NOT IN" at 15;00. At 15:15 however... If you have the inclination continue this and each time see what the table shows. Apologies for being so verbose. Does anyone have an explanation? Is this a known problem? Any solutions? Thanks in advance. Ian -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . |
Is the problem that you have 15;00, not 15:00. When I entered that, all
subsequent showed in . User error mate! -- HTH RP (remove nothere from the email address if mailing direct) "Ian" wrote in message ... Hi, I recently constructed a large spreadsheet for a colleague which, amongst other things samples at intervals the number of staff available (that is not on a break, not on leave , not sick etc.) based on a staff roster. For example at 10:15, 10:30, etc how many staff are available to take calls during that interval. A very simplified version of the problem is as follows: Row 2 Column A = a list of names Column B = each member of staffs shift start time i.e. 08:00 Column C = each member of staffs shift end time i.e. 16:00 Row 1, starting at Column D is filled with each interval i.e. 08:00, 08;15, 08:30, 08:45..up to, for example, 20:00. The following formula is entered into each cell starting at row 2, column D up to the final interval, in this case 20:00, for each name - constructing a kind of truth table. =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Seems straightforward enough, I hope. This works as expected - but only up to a point! If you constuct this table and enter the following data you may encounter an unexpected problem. For the 1st staff member Column A row 2 give a start time of 08:00 and an end time of 14:45. This should show correct. The staff member is showing as "NOT IN" at the 14:45 interval. Now increase the end time for the same staff member by 00:15 intervals. At 15:00 the table still reads correctly - "NOT IN" at 15;00. At 15:15 however... If you have the inclination continue this and each time see what the table shows. Apologies for being so verbose. Does anyone have an explanation? Is this a known problem? Any solutions? Thanks in advance. Ian |
Hello Bob,
No. Not user error! -----Original Message----- Is the problem that you have 15;00, not 15:00. When I entered that, all subsequent showed in . User error mate! -- HTH RP (remove nothere from the email address if mailing direct) "Ian" wrote in message ... Hi, I recently constructed a large spreadsheet for a colleague which, amongst other things samples at intervals the number of staff available (that is not on a break, not on leave , not sick etc.) based on a staff roster. For example at 10:15, 10:30, etc how many staff are available to take calls during that interval. A very simplified version of the problem is as follows: Row 2 Column A = a list of names Column B = each member of staffs shift start time i.e. 08:00 Column C = each member of staffs shift end time i.e. 16:00 Row 1, starting at Column D is filled with each interval i.e. 08:00, 08;15, 08:30, 08:45..up to, for example, 20:00. The following formula is entered into each cell starting at row 2, column D up to the final interval, in this case 20:00, for each name - constructing a kind of truth table. =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Seems straightforward enough, I hope. This works as expected - but only up to a point! If you constuct this table and enter the following data you may encounter an unexpected problem. For the 1st staff member Column A row 2 give a start time of 08:00 and an end time of 14:45. This should show correct. The staff member is showing as "NOT IN" at the 14:45 interval. Now increase the end time for the same staff member by 00:15 intervals. At 15:00 the table still reads correctly - "NOT IN" at 15;00. At 15:15 however... If you have the inclination continue this and each time see what the table shows. Apologies for being so verbose. Does anyone have an explanation? Is this a known problem? Any solutions? Thanks in advance. Ian . |
|
If I manually type 15:15:00 in the relevant cell and do
not use the formula it behaves as it ought to. This doesn't help as I want to be able to alter the interval and sample using custom samples. Part of the original workbook used more complex formulas to construct the interval patterns. It was only when the intervals were made more simple that the error came to light casting (embarrassingly) into doubt everything that preceded it. -----Original Message----- Hello Debra, Thank you. You are right. In order to increase the flexibility of the sheet the interval headings reference an opening time cell and an interval cell. The first heading (D1) references the opening time cell and the other interval headings are constructed using the formulas (in E1) =d$1+interval cell, (in F1) =e$1+interval cell etc. I have been careful to format the cells with a custom format hh:mm:ss. The interval cell for this example contains 00:15:00 formatted hh:mm:ss. I want to be able to sample using a variety of intervals. You've found the source of the problem - but not the solution. How does this inaccuracy creep in? What can be done to gaurd against? Excel not to be relied on? What seems a very straightforward application of excel has caused me..grief! Thanks for your help. Ian -----Original Message----- How did you enter the times in row 1? If they're formulas, they may be slightly greater or less than the time that's being displayed. You could type the first two numbers, and select them Then point to the fill handle (the small black square at the bottom right of the selection) When the pointer changes to a black plus sign, drag across, to the last column Ian wrote: Hi, I recently constructed a large spreadsheet for a colleague which, amongst other things samples at intervals the number of staff available (that is not on a break, not on leave , not sick etc.) based on a staff roster. For example at 10:15, 10:30, etc how many staff are available to take calls during that interval. A very simplified version of the problem is as follows: Row 2 Column A = a list of names Column B = each member of staffs shift start time i.e. 08:00 Column C = each member of staffs shift end time i.e. 16:00 Row 1, starting at Column D is filled with each interval i.e. 08:00, 08;15, 08:30, 08:45..up to, for example, 20:00. The following formula is entered into each cell starting at row 2, column D up to the final interval, in this case 20:00, for each name - constructing a kind of truth table. =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Seems straightforward enough, I hope. This works as expected - but only up to a point! If you constuct this table and enter the following data you may encounter an unexpected problem. For the 1st staff member Column A row 2 give a start time of 08:00 and an end time of 14:45. This should show correct. The staff member is showing as "NOT IN" at the 14:45 interval. Now increase the end time for the same staff member by 00:15 intervals. At 15:00 the table still reads correctly - "NOT IN" at 15;00. At 15:15 however... If you have the inclination continue this and each time see what the table shows. Apologies for being so verbose. Does anyone have an explanation? Is this a known problem? Any solutions? Thanks in advance. Ian -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . . |
If you round the numbers, does it give the results you expect? For example:
=IF(AND(ROUND(D$1,10)=ROUND($B2,10),ROUND(D$1,10) <ROUND($C2,10)), "IN","NOT IN") Ian wrote: Hello Debra, Thank you. You are right. In order to increase the flexibility of the sheet the interval headings reference an opening time cell and an interval cell. The first heading (D1) references the opening time cell and the other interval headings are constructed using the formulas (in E1) =d$1+interval cell, (in F1) =e$1+interval cell etc. I have been careful to format the cells with a custom format hh:mm:ss. The interval cell for this example contains 00:15:00 formatted hh:mm:ss. I want to be able to sample using a variety of intervals. You've found the source of the problem - but not the solution. How does this inaccuracy creep in? What can be done to gaurd against? Excel not to be relied on? What seems a very straightforward application of excel has caused me..grief! Thanks for your help. Ian -----Original Message----- How did you enter the times in row 1? If they're formulas, they may be slightly greater or less than the time that's being displayed. You could type the first two numbers, and select them Then point to the fill handle (the small black square at the bottom right of the selection) When the pointer changes to a black plus sign, drag across, to the last column Ian wrote: Hi, I recently constructed a large spreadsheet for a colleague which, amongst other things samples at intervals the number of staff available (that is not on a break, not on leave , not sick etc.) based on a staff roster. For example at 10:15, 10:30, etc how many staff are available to take calls during that interval. A very simplified version of the problem is as follows: Row 2 Column A = a list of names Column B = each member of staffs shift start time i.e. 08:00 Column C = each member of staffs shift end time i.e. 16:00 Row 1, starting at Column D is filled with each interval i.e. 08:00, 08;15, 08:30, 08:45..up to, for example, 20:00. The following formula is entered into each cell starting at row 2, column D up to the final interval, in this case 20:00, for each name - constructing a kind of truth table. =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Seems straightforward enough, I hope. This works as expected - but only up to a point! If you constuct this table and enter the following data you may encounter an unexpected problem. For the 1st staff member Column A row 2 give a start time of 08:00 and an end time of 14:45. This should show correct. The staff member is showing as "NOT IN" at the 14:45 interval. Now increase the end time for the same staff member by 00:15 intervals. At 15:00 the table still reads correctly - "NOT IN" at 15;00. At 15:15 however... If you have the inclination continue this and each time see what the table shows. Apologies for being so verbose. Does anyone have an explanation? Is this a known problem? Any solutions? Thanks in advance. Ian -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Come on guys, lets not blame the man without testing his statement!
Using Ian's formula, the following results were achieved: 15:15 = Shows the member as In, iso Not in, WHICH IS WRONG 15:30 and 15:45, shows as Not In, which is correct 16:00 shows as In iso Not In, WHICH IS WRONG 16:15 and 16:30 shows as not in, which is correct 16:45 shows as In iso Not in, WHICH IS WRONG 17:00 shows as Not in, which is correct I cannot explain this, but Ian is correct, there is something wrong here. I am using Microsoft Office XP, SP3 "Ian" wrote: Hello Bob, No. Not user error! -----Original Message----- Is the problem that you have 15;00, not 15:00. When I entered that, all subsequent showed in . User error mate! -- HTH RP (remove nothere from the email address if mailing direct) "Ian" wrote in message ... Hi, I recently constructed a large spreadsheet for a colleague which, amongst other things samples at intervals the number of staff available (that is not on a break, not on leave , not sick etc.) based on a staff roster. For example at 10:15, 10:30, etc how many staff are available to take calls during that interval. A very simplified version of the problem is as follows: Row 2 Column A = a list of names Column B = each member of staffs shift start time i.e. 08:00 Column C = each member of staffs shift end time i.e. 16:00 Row 1, starting at Column D is filled with each interval i.e. 08:00, 08;15, 08:30, 08:45..up to, for example, 20:00. The following formula is entered into each cell starting at row 2, column D up to the final interval, in this case 20:00, for each name - constructing a kind of truth table. =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Seems straightforward enough, I hope. This works as expected - but only up to a point! If you constuct this table and enter the following data you may encounter an unexpected problem. For the 1st staff member Column A row 2 give a start time of 08:00 and an end time of 14:45. This should show correct. The staff member is showing as "NOT IN" at the 14:45 interval. Now increase the end time for the same staff member by 00:15 intervals. At 15:00 the table still reads correctly - "NOT IN" at 15;00. At 15:15 however... If you have the inclination continue this and each time see what the table shows. Apologies for being so verbose. Does anyone have an explanation? Is this a known problem? Any solutions? Thanks in advance. Ian . |
|
Thanks again Debra.
The more I look at it the more puzzled I get. I want to understand it more fully if I can. Using Round() does seem to work but now I doubt any "fix". As I mentioned the workbook when live is fairly complex and it would be impractical to have to test it to destruction in order to be sure it works. Further, the live workbook tests a large number of conditions for each staff member at each interval the actual formula used is almost at, correct me if I'm wrong, Excel's limit of 1024 chacters. Using round may be too much! Cheers, Ian -----Original Message----- If you round the numbers, does it give the results you expect? For example: =IF(AND(ROUND(D$1,10)=ROUND($B2,10),ROUND(D$1,10 )<ROUND ($C2,10)), "IN","NOT IN") Ian wrote: Hello Debra, Thank you. You are right. In order to increase the flexibility of the sheet the interval headings reference an opening time cell and an interval cell. The first heading (D1) references the opening time cell and the other interval headings are constructed using the formulas (in E1) =d$1+interval cell, (in F1) =e$1+interval cell etc. I have been careful to format the cells with a custom format hh:mm:ss. The interval cell for this example contains 00:15:00 formatted hh:mm:ss. I want to be able to sample using a variety of intervals. You've found the source of the problem - but not the solution. How does this inaccuracy creep in? What can be done to gaurd against? Excel not to be relied on? What seems a very straightforward application of excel has caused me..grief! Thanks for your help. Ian -----Original Message----- How did you enter the times in row 1? If they're formulas, they may be slightly greater or less than the time that's being displayed. You could type the first two numbers, and select them Then point to the fill handle (the small black square at the bottom right of the selection) When the pointer changes to a black plus sign, drag across, to the last column Ian wrote: Hi, I recently constructed a large spreadsheet for a colleague which, amongst other things samples at intervals the number of staff available (that is not on a break, not on leave , not sick etc.) based on a staff roster. For example at 10:15, 10:30, etc how many staff are available to take calls during that interval. A very simplified version of the problem is as follows: Row 2 Column A = a list of names Column B = each member of staffs shift start time i.e. 08:00 Column C = each member of staffs shift end time i.e. 16:00 Row 1, starting at Column D is filled with each interval i.e. 08:00, 08;15, 08:30, 08:45..up to, for example, 20:00. The following formula is entered into each cell starting at row 2, column D up to the final interval, in this case 20:00, for each name - constructing a kind of truth table. =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Seems straightforward enough, I hope. This works as expected - but only up to a point! If you constuct this table and enter the following data you may encounter an unexpected problem. For the 1st staff member Column A row 2 give a start time of 08:00 and an end time of 14:45. This should show correct. The staff member is showing as "NOT IN" at the 14:45 interval. Now increase the end time for the same staff member by 00:15 intervals. At 15:00 the table still reads correctly - "NOT IN" at 15;00. At 15:15 however... If you have the inclination continue this and each time see what the table shows. Apologies for being so verbose. Does anyone have an explanation? Is this a known problem? Any solutions? Thanks in advance. Ian -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . |
If anyone's still listening..
If I Round() the result of the interval calculation formula =ROUND(D$2+INTERVAL,10) i seem on first examination to get the correct results. But unless I know what is going wrong, or without those impractical, exhausting exhaustive tests, can I be sure? Also, what exactly does Round(time, 10) do to a cell/value containing formatted time hh:mm:ss? Why 10? Does some kind of overflow occur somewhere within time arithmetic? (One for Dr Who) Thank you and good might! Ian -----Original Message----- If you round the numbers, does it give the results you expect? For example: =IF(AND(ROUND(D$1,10)=ROUND($B2,10),ROUND(D$1,10 )<ROUND ($C2,10)), "IN","NOT IN") Ian wrote: Hello Debra, Thank you. You are right. In order to increase the flexibility of the sheet the interval headings reference an opening time cell and an interval cell. The first heading (D1) references the opening time cell and the other interval headings are constructed using the formulas (in E1) =d$1+interval cell, (in F1) =e$1+interval cell etc. I have been careful to format the cells with a custom format hh:mm:ss. The interval cell for this example contains 00:15:00 formatted hh:mm:ss. I want to be able to sample using a variety of intervals. You've found the source of the problem - but not the solution. How does this inaccuracy creep in? What can be done to gaurd against? Excel not to be relied on? What seems a very straightforward application of excel has caused me..grief! Thanks for your help. Ian -----Original Message----- How did you enter the times in row 1? If they're formulas, they may be slightly greater or less than the time that's being displayed. You could type the first two numbers, and select them Then point to the fill handle (the small black square at the bottom right of the selection) When the pointer changes to a black plus sign, drag across, to the last column Ian wrote: Hi, I recently constructed a large spreadsheet for a colleague which, amongst other things samples at intervals the number of staff available (that is not on a break, not on leave , not sick etc.) based on a staff roster. For example at 10:15, 10:30, etc how many staff are available to take calls during that interval. A very simplified version of the problem is as follows: Row 2 Column A = a list of names Column B = each member of staffs shift start time i.e. 08:00 Column C = each member of staffs shift end time i.e. 16:00 Row 1, starting at Column D is filled with each interval i.e. 08:00, 08;15, 08:30, 08:45..up to, for example, 20:00. The following formula is entered into each cell starting at row 2, column D up to the final interval, in this case 20:00, for each name - constructing a kind of truth table. =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Seems straightforward enough, I hope. This works as expected - but only up to a point! If you constuct this table and enter the following data you may encounter an unexpected problem. For the 1st staff member Column A row 2 give a start time of 08:00 and an end time of 14:45. This should show correct. The staff member is showing as "NOT IN" at the 14:45 interval. Now increase the end time for the same staff member by 00:15 intervals. At 15:00 the table still reads correctly - "NOT IN" at 15;00. At 15:15 however... If you have the inclination continue this and each time see what the table shows. Apologies for being so verbose. Does anyone have an explanation? Is this a known problem? Any solutions? Thanks in advance. Ian -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . |
I thank you
-----Original Message----- Come on guys, lets not blame the man without testing his statement! Using Ian's formula, the following results were achieved: 15:15 = Shows the member as In, iso Not in, WHICH IS WRONG 15:30 and 15:45, shows as Not In, which is correct 16:00 shows as In iso Not In, WHICH IS WRONG 16:15 and 16:30 shows as not in, which is correct 16:45 shows as In iso Not in, WHICH IS WRONG 17:00 shows as Not in, which is correct I cannot explain this, but Ian is correct, there is something wrong here. I am using Microsoft Office XP, SP3 "Ian" wrote: Hello Bob, No. Not user error! -----Original Message----- Is the problem that you have 15;00, not 15:00. When I entered that, all subsequent showed in . User error mate! -- HTH RP (remove nothere from the email address if mailing direct) "Ian" wrote in message ... Hi, I recently constructed a large spreadsheet for a colleague which, amongst other things samples at intervals the number of staff available (that is not on a break, not on leave , not sick etc.) based on a staff roster. For example at 10:15, 10:30, etc how many staff are available to take calls during that interval. A very simplified version of the problem is as follows: Row 2 Column A = a list of names Column B = each member of staffs shift start time i.e. 08:00 Column C = each member of staffs shift end time i.e. 16:00 Row 1, starting at Column D is filled with each interval i.e. 08:00, 08;15, 08:30, 08:45..up to, for example, 20:00. The following formula is entered into each cell starting at row 2, column D up to the final interval, in this case 20:00, for each name - constructing a kind of truth table. =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Seems straightforward enough, I hope. This works as expected - but only up to a point! If you constuct this table and enter the following data you may encounter an unexpected problem. For the 1st staff member Column A row 2 give a start time of 08:00 and an end time of 14:45. This should show correct. The staff member is showing as "NOT IN" at the 14:45 interval. Now increase the end time for the same staff member by 00:15 intervals. At 15:00 the table still reads correctly - "NOT IN" at 15;00. At 15:15 however... If you have the inclination continue this and each time see what the table shows. Apologies for being so verbose. Does anyone have an explanation? Is this a known problem? Any solutions? Thanks in advance. Ian . . |
Chip Pearson has information on Date and Time arithmetic that may help
you, including this section on rounding times: http://www.cpearson.com/excel/datear...#RoundingTimes Ian wrote: If anyone's still listening.. If I Round() the result of the interval calculation formula =ROUND(D$2+INTERVAL,10) i seem on first examination to get the correct results. But unless I know what is going wrong, or without those impractical, exhausting exhaustive tests, can I be sure? Also, what exactly does Round(time, 10) do to a cell/value containing formatted time hh:mm:ss? Why 10? Does some kind of overflow occur somewhere within time arithmetic? (One for Dr Who) Thank you and good might! Ian -----Original Message----- If you round the numbers, does it give the results you expect? For example: =IF(AND(ROUND(D$1,10)=ROUND($B2,10),ROUND(D$1,1 0)<ROUND ($C2,10)), "IN","NOT IN") Ian wrote: Hello Debra, Thank you. You are right. In order to increase the flexibility of the sheet the interval headings reference an opening time cell and an interval cell. The first heading (D1) references the opening time cell and the other interval headings are constructed using the formulas (in E1) =d$1+interval cell, (in F1) =e$1+interval cell etc. I have been careful to format the cells with a custom format hh:mm:ss. The interval cell for this example contains 00:15:00 formatted hh:mm:ss. I want to be able to sample using a variety of intervals. You've found the source of the problem - but not the solution. How does this inaccuracy creep in? What can be done to gaurd against? Excel not to be relied on? What seems a very straightforward application of excel has caused me..grief! Thanks for your help. Ian -----Original Message----- How did you enter the times in row 1? If they're formulas, they may be slightly greater or less than the time that's being displayed. You could type the first two numbers, and select them Then point to the fill handle (the small black square at the bottom right of the selection) When the pointer changes to a black plus sign, drag across, to the last column Ian wrote: Hi, I recently constructed a large spreadsheet for a colleague which, amongst other things samples at intervals the number of staff available (that is not on a break, not on leave , not sick etc.) based on a staff roster. For example at 10:15, 10:30, etc how many staff are available to take calls during that interval. A very simplified version of the problem is as follows: Row 2 Column A = a list of names Column B = each member of staffs shift start time i.e. 08:00 Column C = each member of staffs shift end time i.e. 16:00 Row 1, starting at Column D is filled with each interval i.e. 08:00, 08;15, 08:30, 08:45..up to, for example, 20:00. The following formula is entered into each cell starting at row 2, column D up to the final interval, in this case 20:00, for each name - constructing a kind of truth table. =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Seems straightforward enough, I hope. This works as expected - but only up to a point! If you constuct this table and enter the following data you may encounter an unexpected problem. For the 1st staff member Column A row 2 give a start time of 08:00 and an end time of 14:45. This should show correct. The staff member is showing as "NOT IN" at the 14:45 interval. Now increase the end time for the same staff member by 00:15 intervals. At 15:00 the table still reads correctly - "NOT IN" at 15;00. At 15:15 however... If you have the inclination continue this and each time see what the table shows. Apologies for being so verbose. Does anyone have an explanation? Is this a known problem? Any solutions? Thanks in advance. Ian -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Hi!
It might be worth looking at the actual values stored in row 1. I guess they are formatted as time. But what are the dates associated with them? Your time comparisons could fall if the date bit is different. hey could even be harking back to 1900. Alf |
I was able to duplicate the problem if the opening time cell was set as 8:00
AM. To correct the problem I set calculation / Precision as displayed under Tools / Options with a checkmark although the resulting popup states 'Data will permanently lose accuracy'. Perhaps this has something to do with 8:00 AM being exactly one third of a day. Hope this helps. "Ian" wrote in message ... I thank you -----Original Message----- Come on guys, lets not blame the man without testing his statement! Using Ian's formula, the following results were achieved: 15:15 = Shows the member as In, iso Not in, WHICH IS WRONG 15:30 and 15:45, shows as Not In, which is correct 16:00 shows as In iso Not In, WHICH IS WRONG 16:15 and 16:30 shows as not in, which is correct 16:45 shows as In iso Not in, WHICH IS WRONG 17:00 shows as Not in, which is correct I cannot explain this, but Ian is correct, there is something wrong here. I am using Microsoft Office XP, SP3 "Ian" wrote: Hello Bob, No. Not user error! -----Original Message----- Is the problem that you have 15;00, not 15:00. When I entered that, all subsequent showed in . User error mate! -- HTH RP (remove nothere from the email address if mailing direct) "Ian" wrote in message ... Hi, I recently constructed a large spreadsheet for a colleague which, amongst other things samples at intervals the number of staff available (that is not on a break, not on leave , not sick etc.) based on a staff roster. For example at 10:15, 10:30, etc how many staff are available to take calls during that interval. A very simplified version of the problem is as follows: Row 2 Column A = a list of names Column B = each member of staffs shift start time i.e. 08:00 Column C = each member of staffs shift end time i.e. 16:00 Row 1, starting at Column D is filled with each interval i.e. 08:00, 08;15, 08:30, 08:45..up to, for example, 20:00. The following formula is entered into each cell starting at row 2, column D up to the final interval, in this case 20:00, for each name - constructing a kind of truth table. =IF(AND(D$1=$B2,D$1<$C2),"IN","NOT IN") Seems straightforward enough, I hope. This works as expected - but only up to a point! If you constuct this table and enter the following data you may encounter an unexpected problem. For the 1st staff member Column A row 2 give a start time of 08:00 and an end time of 14:45. This should show correct. The staff member is showing as "NOT IN" at the 14:45 interval. Now increase the end time for the same staff member by 00:15 intervals. At 15:00 the table still reads correctly - "NOT IN" at 15;00. At 15:15 however... If you have the inclination continue this and each time see what the table shows. Apologies for being so verbose. Does anyone have an explanation? Is this a known problem? Any solutions? Thanks in advance. Ian . . |
"Kassie" wrote...
Come on guys, lets not blame the man without testing his statement! Using Ian's formula, the following results were achieved: 15:15 = Shows the member as In, iso Not in, WHICH IS WRONG 15:30 and 15:45, shows as Not In, which is correct 16:00 shows as In iso Not In, WHICH IS WRONG .... Using 08:00 as the initial time in D1, I can duplicate these seeming errors. It's just another instance of floating point rounding error. 08:00 is 1/3, which doesn't have an exact binary representation. In this case, text comparisons would be best. Change the D2 formula to =IF(AND(TEXT(D$1,"[hh]mm")=TEXT($B2,"[hh]mm"), TEXT(D$1,"[hh]mm")<TEXT($C2,"[hh]mm")),"IN","NOT IN") [Note intentional omission of colons between hours and minutes.] An alternative would be =IF(MEDIAN($B2-1/86400,D$1,$C2-1/86400)=D$1,"IN","NOT IN") which subtracts 1 second from the employee's starting and ending times, which should be well in excess of the rounding error. |
Ian,
not sure if you're still going to be following this thread, but I had the very same problem a while back. The problem occurs because of the level of precision with which Excel stores the fractions which represent your times. If the values you're testing and the interval values are typed in directly then you're test formulae will work fine, if you use a formula to advance the interval it will cause errors. The reason for this is that both the start time and the interval value are being rounded very slightly. As you repeatedly add the second value, the level of diference increases. Excel is precise to the 14th decimal place, so as long as the differences are below this point you're okay, but when they get to this point it causes the errors you're seeing. If you want to test this, type 0:00:15 in cell A1, 07:00:00 in A3, then "=a3+$A$1" copied down to A26. In column B, type the time values in directly, then in column C put "=A3=B3" to give a boolean comparison. 09:30, 10:15, 10:45 and 11:00 should evaluate FALSE. Now reformat columns A & B as numbers to 15 decimal places and you should see the last digit in the FALSE rows is different. I think Harlan Grove has made some pretty exhaustive posts on the limitations of double-precision floating point representations of fractions. Cheers, Pete |
| All times are GMT +1. The time now is 04:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com