Fill series by hour

For posterity....

HansVogelaar  wrote:  ``In the Fill Series dialog [....] Enter 0.041666666666666666 in the Step box and 0.9583333333333333333 in the Stop``

There is no need to enter decimal approximations.  The Fill dialog accepts 1:00 and 23:00.

Moreover, the time syntax results in a more accurate result, as demonstrated in the image below.

Besides, it is useless to enter more than 15 significant digits, since that is all that Excel parses.

And BTW, the correct 17-significant-digit approximations are 0.041666666666666602 and 0.95833333333333304.

(17-significant-digit approximations are the necessary and sufficient precision to use for converting between decimal and binary representations with no loss of precision, according to the IEEE 754 standard).

-----

Be that as it may, the most accurate way to add time (or any decimal fraction) down a column is with a formula of the following form, which explicitly rounds to the desired precision:

=--TEXT(B2+"1:0", "h:m")

The following image compares the accuracy of the various ways (*) to propagate a fractional change.

(Errata....  I should have written "drag one cell" and "drag two cells", not "handle".)

The decimal comparison is of the form =D2=$A2, as we might rely on in IF and SUMIF formulas.

The binary match is of the form =ISNUMBER(MATCH(D2,$A2,0)), as we might rely on in MATCH, LOOKUP and RANK formulas.

(The difference between decimal comparisons and binary matches is due to a misguided "feature" that is unique to Excel and work-alike apps.  It is a half-baked attempt to hide infinistesimal differences.  It is non-standard.)

Note that all methods except adding a decimal time approximation (column D) produce results that are TRUE for all decimal comparisons.

(.... In this short example.  Decimal comparison errors might occur over longer periods of time and/or with smaller increments, notably one second.)

But only the formula method produces results that are TRUE for all binary matches.

-----

(*) The "one-cell" drag method is the method described by OliverScheurich.   It seems to be limited to incrementing a time constant by one hour.

The "two-cell" drag method is the more-general drag method.  Enter two or more constant values (0:00 and 1:00), select the constant cells, and drag the handle in the last cell down the column.

In general, the last constant is increment by the slope of the best-fit linear regression of the constant values.

For two constants, that is simply the difference between the first two constants.

OliverScheurich 

Wow, I never knew you could do that!  I figured there must be a simple way, I just couldn't figure it out.  Thanks so much!