Hi Folks!

Here's a strange one for you.

Two cells with the same entry formatted as h:mm.

F1 = 23:00
C1 = 23:00

These are not calculated values.

Doing a simple Sumproduct and got incorrect results.
Traced back to the evaluation that F1 < C1.

I formatted both cells as GENERAL.

F1 = 0.958333333333334
C1 = 0.958333333333333

I then tried entering pairs of 23:00 in random cells but
could not reproduce the above.

Biff

Are they hardcoded results from what were previously calculated values?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Biff" wrote in message
...
Hi Folks!

Here's a strange one for you.

Two cells with the same entry formatted as h:mm.

F1 = 23:00
C1 = 23:00

These are not calculated values.

Doing a simple Sumproduct and got incorrect results.
Traced back to the evaluation that F1 < C1.

I formatted both cells as GENERAL.

F1 = 0.958333333333334
C1 = 0.958333333333333

I then tried entering pairs of 23:00 in random cells but
could not reproduce the above.

Biff

Hi Ken!

No.

C1 is simply an entered value.

F1 is a DV dropdown. The source is a range - H1:H19.

H1 = 6:00 (not calculated) formatted as h:mm.

H1 was dragged down to H19 and filled as a series.

Biff

-----Original Message-----
Are they hardcoded results from what were previously
calculated values?

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------
------------------
It's easier to beg forgiveness than ask
permission :-)
----------------------------------------------------------
------------------

"Biff" wrote in message
...
Hi Folks!

Here's a strange one for you.

Two cells with the same entry formatted as h:mm.

F1 = 23:00
C1 = 23:00

These are not calculated values.

Doing a simple Sumproduct and got incorrect results.
Traced back to the evaluation that F1 < C1.

I formatted both cells as GENERAL.

F1 = 0.958333333333334
C1 = 0.958333333333333

I then tried entering pairs of 23:00 in random cells but
could not reproduce the above.

Biff


.

Hi Biff,

Just for information - I have no answers - the same happens to me in XL97.
I assume that the 'Fill Series' is akin to indexing it with a formula, ie XL
uses some sort of calculation to index the next time, so it is open to
rounding errors. FWIT I get 0.916666667 in general format for the 23:00
time when series filled and 0.958333333 when directly entered

Regards

Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Biff" wrote in message
...
Hi Ken!

No.

C1 is simply an entered value.

F1 is a DV dropdown. The source is a range - H1:H19.

H1 = 6:00 (not calculated) formatted as h:mm.

H1 was dragged down to H19 and filled as a series.

Biff

-----Original Message-----
Are they hardcoded results from what were previously
calculated values?

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------
------------------
It's easier to beg forgiveness than ask
permission :-)
----------------------------------------------------------
------------------

"Biff" wrote in message
...
Hi Folks!

Here's a strange one for you.

Two cells with the same entry formatted as h:mm.

F1 = 23:00
C1 = 23:00

These are not calculated values.

Doing a simple Sumproduct and got incorrect results.
Traced back to the evaluation that F1 < C1.

I formatted both cells as GENERAL.

F1 = 0.958333333333334
C1 = 0.958333333333333

I then tried entering pairs of 23:00 in random cells but
could not reproduce the above.

Biff


.

Hi Sandy!

I was thinking along those same lines. But here's
something that's even more strange.

Cell H18 was the filled series value formatted as h:mm,
23:00.

I cleared that cell and typed in 23:00. The GENERAL format
value of that cell is now 0.958333333333333 but, when the
corresponding value from the dropdown in cell F1 is
selected, it still GENERAL formats to 0.958333333333334. I
even tried re-entering the source for the dropdown.

Then, in cells G1:G18 I manually entered the times from
6:00 to 23:00. Setup a DV dropdown for that range and
everything works just fine.

It seems as though the source for the dropdown also uses
an indexed calculation based on the first cell of the
source range if it's a series filled range.

Thanks for taking the time to look at this!

Biff

-----Original Message-----
Hi Biff,

Just for information - I have no answers - the same
happens to me in XL97.
I assume that the 'Fill Series' is akin to indexing it
with a formula, ie XL
uses some sort of calculation to index the next time, so
it is open to
rounding errors. FWIT I get 0.916666667 in general
format for the 23:00
time when series filled and 0.958333333 when directly
entered

Regards

Sandy

--
to e-mail direct replace @mailinator.com with
@tiscali.co.uk


"Biff" wrote in message
...
Hi Ken!

No.

C1 is simply an entered value.

F1 is a DV dropdown. The source is a range - H1:H19.

H1 = 6:00 (not calculated) formatted as h:mm.

H1 was dragged down to H19 and filled as a series.

Biff

-----Original Message-----
Are they hardcoded results from what were previously
calculated values?

--
Regards
Ken....................... Microsoft
MVP -
Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

-------------------------------------------------------
---
------------------
It's easier to beg forgiveness than
ask
permission :-)
-------------------------------------------------------
---
------------------

"Biff" wrote in message
...
Hi Folks!

Here's a strange one for you.

Two cells with the same entry formatted as h:mm.

F1 = 23:00
C1 = 23:00

These are not calculated values.

Doing a simple Sumproduct and got incorrect results.
Traced back to the evaluation that F1 < C1.

I formatted both cells as GENERAL.

F1 = 0.958333333333334
C1 = 0.958333333333333

I then tried entering pairs of 23:00 in random cells
but
could not reproduce the above.

Biff


.




.

Hi Biff,

Yes that is strange but unfortunately, (or perhaps fortunately for me), it
does not happen for me. If I series fill I get the error, if I directly
enter I do not. Perhaps it's version dependant. Maybe others will report
what they get in their version of XL

Regards

Sansy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Biff" wrote in message
...
Hi Sandy!

I was thinking along those same lines. But here's
something that's even more strange.

Cell H18 was the filled series value formatted as h:mm,
23:00.

I cleared that cell and typed in 23:00. The GENERAL format
value of that cell is now 0.958333333333333 but, when the
corresponding value from the dropdown in cell F1 is
selected, it still GENERAL formats to 0.958333333333334. I
even tried re-entering the source for the dropdown.

Then, in cells G1:G18 I manually entered the times from
6:00 to 23:00. Setup a DV dropdown for that range and
everything works just fine.

It seems as though the source for the dropdown also uses
an indexed calculation based on the first cell of the
source range if it's a series filled range.

Thanks for taking the time to look at this!

Biff

-----Original Message-----
Hi Biff,

Just for information - I have no answers - the same
happens to me in XL97.
I assume that the 'Fill Series' is akin to indexing it
with a formula, ie XL
uses some sort of calculation to index the next time, so
it is open to
rounding errors. FWIT I get 0.916666667 in general
format for the 23:00
time when series filled and 0.958333333 when directly
entered

Regards

Sandy

--
to e-mail direct replace @mailinator.com with
@tiscali.co.uk


"Biff" wrote in message
...
Hi Ken!

No.

C1 is simply an entered value.

F1 is a DV dropdown. The source is a range - H1:H19.

H1 = 6:00 (not calculated) formatted as h:mm.

H1 was dragged down to H19 and filled as a series.

Biff

-----Original Message-----
Are they hardcoded results from what were previously
calculated values?

--
Regards
Ken....................... Microsoft
MVP -
Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

-------------------------------------------------------
---
------------------
It's easier to beg forgiveness than
ask
permission :-)
-------------------------------------------------------
---
------------------

"Biff" wrote in message
...
Hi Folks!

Here's a strange one for you.

Two cells with the same entry formatted as h:mm.

F1 = 23:00
C1 = 23:00

These are not calculated values.

Doing a simple Sumproduct and got incorrect results.
Traced back to the evaluation that F1 < C1.

I formatted both cells as GENERAL.

F1 = 0.958333333333334
C1 = 0.958333333333333

I then tried entering pairs of 23:00 in random cells
but
could not reproduce the above.

Biff


.




.

On Sat, 12 Mar 2005 11:00:03 -0800, "Biff" wrote:

Hi Folks!

Here's a strange one for you.

Two cells with the same entry formatted as h:mm.

F1 = 23:00
C1 = 23:00

These are not calculated values.

Doing a simple Sumproduct and got incorrect results.
Traced back to the evaluation that F1 < C1.

I formatted both cells as GENERAL.

F1 = 0.958333333333334
C1 = 0.958333333333333

I then tried entering pairs of 23:00 in random cells but
could not reproduce the above.

Biff

Biff,

I tried to reproduce your problem with XL2002 and was unable.

I entered 6:00 in H1 and filled it down to H19.
I set up F1 with a dropdown list using Data Validation and referencing the
H1:H19 range.

I selected 23:00 from the dropdown.

It came out as 0.95833333333333300

Maybe it's a problem in your version of XL?


--ron

Hi Ron!

I'm also using XL2002.

???

Biff

-----Original Message-----
On Sat, 12 Mar 2005 11:00:03 -0800, "Biff"
wrote:

Hi Folks!

Here's a strange one for you.

Two cells with the same entry formatted as h:mm.

F1 = 23:00
C1 = 23:00

These are not calculated values.

Doing a simple Sumproduct and got incorrect results.
Traced back to the evaluation that F1 < C1.

I formatted both cells as GENERAL.

F1 = 0.958333333333334
C1 = 0.958333333333333

I then tried entering pairs of 23:00 in random cells but
could not reproduce the above.

Biff

Biff,

I tried to reproduce your problem with XL2002 and was
unable.

I entered 6:00 in H1 and filled it down to H19.
I set up F1 with a dropdown list using Data Validation
and referencing the
H1:H19 range.

I selected 23:00 from the dropdown.

It came out as 0.95833333333333300

Maybe it's a problem in your version of XL?


--ron
.

On Sat, 12 Mar 2005 20:59:09 -0800, "Bif" wrote:

Hi Ron!

I'm also using XL2002.

???

Biff

Well, if you cannot reproduce the problem, then perhaps there's something about
the original data.

My full version designation is Excel 2002(10.6501.6714) SP3.

Do you have SP3?


--ron

Full version:

Excel 2002(10.6501.6735) SP3

I think Sandy is correct, however, after I cleared the
cell and manually entered the value 23:00, it still is not
working.

Want to see the file? It's small, only 17kb.

Biff

-----Original Message-----
On Sat, 12 Mar 2005 20:59:09 -0800, "Bif"
wrote:

Hi Ron!

I'm also using XL2002.

???

Biff

Well, if you cannot reproduce the problem, then perhaps
there's something about
the original data.

My full version designation is Excel 2002(10.6501.6714)
SP3.

Do you have SP3?


--ron
.

On Sun, 13 Mar 2005 12:22:16 -0800, "Biff" wrote:

Full version:

Excel 2002(10.6501.6735) SP3

I think Sandy is correct, however, after I cleared the
cell and manually entered the value 23:00, it still is not
working.

Want to see the file? It's small, only 17kb.

Biff


Yes, I would.

Email it to me, after reversing the address and making the obvious
substitution:

moc.enilnodlefnesorTAzyx





--ron

By starting with 6:00 in H1 and dragging down, you get calculated
results in H2:H19. Therefore you get a calculated result in F1.

1:00=1/24 (the increment in the drag down series) has no exact
representation in binary, and hence must be approximated. Since the
increment is approximate, it should not be surprising that the result of
the calculations with the increment are also only approximate.

Jerry

Biff wrote:

Hi Ken!

No.

C1 is simply an entered value.

F1 is a DV dropdown. The source is a range - H1:H19.

H1 = 6:00 (not calculated) formatted as h:mm.

H1 was dragged down to H19 and filled as a series.

Biff


-----Original Message-----
Are they hardcoded results from what were previously

calculated values?

--
Regards
Ken....................... Microsoft MVP -

Excel

Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------

------------------

It's easier to beg forgiveness than ask

permission :-)

----------------------------------------------------------

------------------

"Biff" wrote in message
...

Hi Folks!

Here's a strange one for you.

Two cells with the same entry formatted as h:mm.

F1 = 23:00
C1 = 23:00

These are not calculated values.

Doing a simple Sumproduct and got incorrect results.
Traced back to the evaluation that F1 < C1.

I formatted both cells as GENERAL.

F1 = 0.958333333333334
C1 = 0.958333333333333

I then tried entering pairs of 23:00 in random cells but
could not reproduce the above.

Biff


.

On Sun, 13 Mar 2005 12:22:16 -0800, "Biff" wrote:

Full version:

Excel 2002(10.6501.6735) SP3

I think Sandy is correct, however, after I cleared the
cell and manually entered the value 23:00, it still is not
working.

Want to see the file? It's small, only 17kb.

I fooled around with your file, and with a new workbook.

I started off by entering 6:00 in H1 and filling down to H19. I did the same
thing in Column I. In J1: =H1=I1 and filled down to I19.

Initially, all showed TRUE, as expected.

However, I then selected, for example, I14 (containing 19:00) and dragged it
down to I19. Several of the TRUE's changed to FALSE's. What changed would
depend on where I started.

So my theory is that this is a rounding issue and that you may have, perhaps
inadvertently, done two partial fills when you initially populated the range.
And doing the second fill is what got stuff screwed up.

You may be able to avoid this by using a formula instead of the auto-fill
method (e.g. =TIME(HOUR(H1)+1,0,0) but that will wrap to 0 at midnight, instead
of to "1" as the fill would do.


--ron

Hi Ron!

So my theory is that this is a rounding issue and that
you may have, perhaps inadvertently, done two partial
fills when you initially populated the range. And doing
the second fill is what got stuff screwed up.

Ron, you're a genius!

That's what I did but it was intentional. I drag filled I
don't know how many cells and then later I extended the
list by selecting and dragging the last filled cell.

That would also explain why I could not reproduce the
behavior because when I tried I only did a single drag
operation.

Good stuff!

Thanks for your time.

Biff

-----Original Message-----
On Sun, 13 Mar 2005 12:22:16 -0800, "Biff"
wrote:

Full version:

Excel 2002(10.6501.6735) SP3

I think Sandy is correct, however, after I cleared the
cell and manually entered the value 23:00, it still is
not
working.

Want to see the file? It's small, only 17kb.

I fooled around with your file, and with a new workbook.

I started off by entering 6:00 in H1 and filling down to
H19. I did the same
thing in Column I. In J1: =H1=I1 and filled down to I19.

Initially, all showed TRUE, as expected.

However, I then selected, for example, I14 (containing
19:00) and dragged it
down to I19. Several of the TRUE's changed to FALSE's.
What changed would
depend on where I started.

So my theory is that this is a rounding issue and that
you may have, perhaps
inadvertently, done two partial fills when you initially
populated the range.
And doing the second fill is what got stuff screwed up.

You may be able to avoid this by using a formula instead
of the auto-fill
method (e.g. =TIME(HOUR(H1)+1,0,0) but that will wrap to
0 at midnight, instead
of to "1" as the fill would do.


--ron
.

It's interesting. And I think it could be classified as a "bug" of which one
should be aware. It's certainly not a "design feature" :-)



On Sun, 13 Mar 2005 22:52:25 -0800, "Biff" wrote:

Hi Ron!

So my theory is that this is a rounding issue and that
you may have, perhaps inadvertently, done two partial
fills when you initially populated the range. And doing
the second fill is what got stuff screwed up.

Ron, you're a genius!

That's what I did but it was intentional. I drag filled I
don't know how many cells and then later I extended the
list by selecting and dragging the last filled cell.

That would also explain why I could not reproduce the
behavior because when I tried I only did a single drag
operation.

Good stuff!

Thanks for your time.

Biff

-----Original Message-----
On Sun, 13 Mar 2005 12:22:16 -0800, "Biff"
wrote:

Full version:

Excel 2002(10.6501.6735) SP3

I think Sandy is correct, however, after I cleared the
cell and manually entered the value 23:00, it still is
not
working.

Want to see the file? It's small, only 17kb.

I fooled around with your file, and with a new workbook.

I started off by entering 6:00 in H1 and filling down to
H19. I did the same
thing in Column I. In J1: =H1=I1 and filled down to I19.

Initially, all showed TRUE, as expected.

However, I then selected, for example, I14 (containing
19:00) and dragged it
down to I19. Several of the TRUE's changed to FALSE's.
What changed would
depend on where I started.

So my theory is that this is a rounding issue and that
you may have, perhaps
inadvertently, done two partial fills when you initially
populated the range.
And doing the second fill is what got stuff screwed up.

You may be able to avoid this by using a formula instead
of the auto-fill
method (e.g. =TIME(HOUR(H1)+1,0,0) but that will wrap to
0 at midnight, instead
of to "1" as the fill would do.


--ron
.


--ron

Not a bug, a consequence of finite precision math. The increment of
1:00 = 1/24 = (1/8)*(1/3) which has no exact representation (in either
binary or decimal). Since the increment must be approximated, the
results of dragging down will be approximate. Dragging down appears to
use in the nth row of the drag-down
= start + n*increment
instead of
= start + increment + increment + ... + increment
This reduces accumulated rounding, but also implies that that you may
get different results if you fill the entire range in one or multiple drags.

Jerry

Ron Rosenfeld wrote:

It's interesting. And I think it could be classified as a "bug" of which one
should be aware. It's certainly not a "design feature" :-)



On Sun, 13 Mar 2005 22:52:25 -0800, "Biff" wrote:


Hi Ron!


So my theory is that this is a rounding issue and that
you may have, perhaps inadvertently, done two partial
fills when you initially populated the range. And doing
the second fill is what got stuff screwed up.

Ron, you're a genius!

That's what I did but it was intentional. I drag filled I
don't know how many cells and then later I extended the
list by selecting and dragging the last filled cell.

That would also explain why I could not reproduce the
behavior because when I tried I only did a single drag
operation.

Good stuff!

Thanks for your time.

Biff


-----Original Message-----
On Sun, 13 Mar 2005 12:22:16 -0800, "Biff"

wrote:

Full version:

Excel 2002(10.6501.6735) SP3

I think Sandy is correct, however, after I cleared the
cell and manually entered the value 23:00, it still is

not

working.

Want to see the file? It's small, only 17kb.

I fooled around with your file, and with a new workbook.

I started off by entering 6:00 in H1 and filling down to

H19. I did the same

thing in Column I. In J1: =H1=I1 and filled down to I19.

Initially, all showed TRUE, as expected.

However, I then selected, for example, I14 (containing

19:00) and dragged it

down to I19. Several of the TRUE's changed to FALSE's.

What changed would

depend on where I started.

So my theory is that this is a rounding issue and that

you may have, perhaps

inadvertently, done two partial fills when you initially

populated the range.

And doing the second fill is what got stuff screwed up.

You may be able to avoid this by using a formula instead

of the auto-fill

method (e.g. =TIME(HOUR(H1)+1,0,0) but that will wrap to

0 at midnight, instead

of to "1" as the fill would do.


--ron
.



--ron

On Mon, 14 Mar 2005 07:44:26 -0500, "Jerry W. Lewis"
wrote:

Not a bug, a consequence of finite precision math.

I agree. But, especially since this is a business-related program, I feel
there should be adequate documentation of this phenomenon, at least in the HELP
section describing the dragging process.


--ron