Excel formula returns incorrect result

[C. Cirelli]

Have a simple formula for calculating the difference in
minutes between two times in a spreadsheet that
occasionally miscalculates a whole number difference and
returns the result as a whole number with a long
decimal. For example, the difference in minutes between
16:10 and 17:00 is obviously 50 minutes, but the formula
[=(D447-C447)*1440] for this calculation produces a
result of 50.00000001 minutes.

Any theories about why this is happening? The formula
was copied and pasted into the empty cells of the entire
column when the sheet was created, so there shouldn't be
an error in one cell and not all the others. Formula
reads the same in each cell in the result column. Thanks.

 

[Bernie Deitrick]

C,

That's math, and computers inability to exactly represent some numbers,
along with rounding that may or may not be taking place in your other calcs
that produce the 17:00 and 16:10.

But let's talk Excel. If you entered

17:00

in cell D447, and

16:10

in C447, and then used the formula

D447-C447

You would get 00:50 if you formatted all the cells for time.

Event with your formula, and the above values, you would get
50.000000000000000000000

If you want to control the decimal value of minutes, then use

=ROUND((D447-C447)*1440,2)
or, if you want just minutes
=INT((D447-C447)*1440)

HTH,
Bernie
MS Excel MVP

 

[JE McGimpsey]

You're seeing rounding error. Times are stored as fractional days (e.g.,
16:10 = 0.673611111111111), so you're not really seeing "a whole number
difference" - the difference is fractional, then multiplied by an
integer. This error is unavoidable in spreadsheets using IEEE Math. To
correct it, you can use:

=ROUND((D447-C447)*1440,0)

but see:

http://www.mcgimpsey.com/excel/pennyoff.html

and

http://www.cpearson.com/excel/rounding.htm

 

[Ron Rosenfeld]

Have a simple formula for calculating the difference in
minutes between two times in a spreadsheet that
occasionally miscalculates a whole number difference and
returns the result as a whole number with a long
decimal. For example, the difference in minutes between
16:10 and 17:00 is obviously 50 minutes, but the formula
[=(D447-C447)*1440] for this calculation produces a
result of 50.00000001 minutes.

Any theories about why this is happening? The formula
was copied and pasted into the empty cells of the entire
column when the sheet was created, so there shouldn't be
an error in one cell and not all the others. Formula
reads the same in each cell in the result column. Thanks.

Although Excel's precision limits of 15 digits, along with the impossibility of
representing certain numbers exactly in binary form may play a role, in this
particular instance, it seems more likely that the contents of D447 or C447 are
the result of a formula, rather than raw entry of data. I say this because I
get 50.000000000000000 entering the times into A1 and A2 and replicating your
formula.

However, you could probably round the result to 10^-10, or so, without losing
any significant accuracy.


--ron