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


.




.

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

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