Wrong result on simple division

[Todd Nelson]

GPA spreadsheet: Dividing sum of the weighted grade points (15.00) by the sum of the credits (5.5) gives me a result of 2.00???????????????????? It is dropping the decimal components, not rounding down or up as if 0 decimal place setting.

 

[Ron Rosenfeld]

GPA spreadsheet: Dividing sum of the weighted grade points (15.00) by the sum of the credits (5.5) gives me a result of 2.00???????????????????? It is dropping the decimal components, not rounding down or up as if 0 decimal place setting.

I cannot reproduce your error.

Here, 15 / 5.5 --> 2.81818181818182

So there is something about your data, or your environment, that you are not sharing with us.

Instead of asking 100 questions, trying to figure it out, it would be easiest if you would post a copy of your worksheet, showing the problem, on some publically accessible site (e.g. Skydrive) , and then post the link here.

 

[joeu2004]

GPA spreadsheet: Dividing sum of the weighted grade points
(15.00) by the sum of the credits (5.5) gives me a result of
2.00????????????????????

The simple answer is: whenever you do arithmetic with non-integers (and
just about any division), you should explicitly round to the precision that
your require; for example, 2 decimal places. Simply selecting a format with
2 decimal places is usually not sufficient [1]. Formatting affects only the
appearance (display), not the actual value in the cell.

To explain your results....

First, I presume you mean 7.5 credits, not 5.5. We might expect 15/7.5 to
be 2 because mathematically, that is 15/(15/2) = 2*15/15.

(In contrast, 15/5.5 is 15/(11/2) = 30/11 = 2.7272....)

But =15/7.5 does indeed equal exactly 2.00 in Excel, although I would not
rely on that always be true.

So presumably 15.00 is not a constant, but the result of a computation
("weighted grade points", as you say). If you format that as Number with 13
decimal places, you will probably discover it is not exactly 15.00, but
perhaps as large as about 15.0049999999999.

Note that 15.0049999999999/7.5 is about 2.00666666666665.

This might be the result of simple arithmetic. If you posted how you are
computing the "15.00" weighted grade points -- that is, the individual grade
points and weighting factors -- we could comment further.

Nevertheless, there is something that goes on "under the hood" that causes
usually-infinitesimal "errors" (not really defects). This is the fact that
most non-integers cannot be represented exactly internally due to the way
that Excel stores numbers and performs arithmetic.

That is another reason to explicit round arithmetic involving non-integers.

 

[joeu2004]

Note that 15.0049999999999/7.5 is about 2.00666666666665.

Missed a zero. It is always best to copy-and-paste instead of retyping.

15.0049999999999/7.5 = 2.00066666666665 (approximately).

most non-integers cannot be represented exactly
internally due to the way that Excel stores numbers
and performs arithmetic.

To demonstrate, the __constant__ 15.0049999999999 is really stored
internally as 15.0049999999998,99529257163521833717823028564453125. The
__computed__ value that is displayed as 15.0049999999999 might be slightly
different.

For example, 15.0049999999999/7.5 = 2.00066666666665 is stored internally as
2.00066666666665,327056762180291116237640380859375.

That is different from the constant 2.00066666666665. That latter is stored
internally as 2.00066666666665,0161943152852472849190235137939453125.

The difference is about 3.11E-15; exactly 7*2^-52.