The following has caused me considerable embarassment..can anyone explain?

I

Ian

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
 
G

Guest

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")
 
D

Debra Dalgleish

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
 
I

Ian

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
 
I

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
 
B

Bob Phillips

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)
 
I

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)





.
 
G

Guest

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.
 
D

Debra Dalgleish

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")
 
G

Guest

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
 
G

Guest

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
 
I

Ian

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
 
I

Ian

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


.
 
D

Debra Dalgleish

Perhaps you could use programming to fill in the Row 1 times, based on
the start time and interval, if either variable is changed.

Add a cell, named IntCalc, with the formula:

=TIME(HOUR(startcell+intervalcell),MINUTE(startcell+intervalcell),0)

Then, add the following code to the worksheet module:

'====================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("intervalcell").Address Then
Range("D1").Value = Range("startcell").Value
Range("E1").Value = Range("IntCalc").Value

Range("D1:E1").AutoFill _
Destination:=Range("D1:AZ1"), Type:=xlFillDefault
End If

End Sub
'================================
 
A

AlfD

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
 
Q

Qwerty

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.
 
H

Harlan Grove

Kassie said:
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.
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top