Numbers changing between sheets

[Guest]

On sheet1: Cell C1 has value of the sum of two other cells. Cell C2 has the
sum of two other cells. Cell C3 is the result of adding C1 and C2. All
numbers are to two decimals (Accounting format)
On sheet2: Cell D4 = 'sheet1'!C3 however when calculated, the number in the
cell is to four decimal points and does not equal the number on sheet1 (C3 =
381.97 but D4 = 381.9683). What is causing this? All numbers are entered as
two decimal place numbers and formatted as accounting (it is a budget
program). How do numbers with two decimal places become a smaller number to
four decimal places when referenced from one sheet to another? This was
written in Excel 2007 and run in Excel 2007.

 

[Guest]

I have not been able to replicate the problem although I have seen a previous
post returning inaccurate currency values in VBA where there is a method of
overcoming the problem.

I would be interested in the actual values in each of the cells to produce
the results you are getting. However, a work around might be to use:-

=ROUND(Sheet1!C3,2)

Regards,

OssieMac

 

[Guest]

ah, "ROUND", why didn't I think of that! That worked just fine.

Further history...
I copy and pasted the worksheets to a new book and still had the problem. I
then opened another workbook and manually rekeyed the data/formulas and that
workbook worked fine. In the original workbook there is heavy formatting and
the fields are not always in the same location so maybe it has something to
do with that. However round rectified the problem in the original workbook
and is a valid solution. Thank you for your time and thoughts on this.

 

[Wondering]

You're confusing internal representation with formatted numbers. When you do
calculations in Excel, the number of places of decimal changes to follow the
rules of mathematics. For example if you multiply a number with 2 places of
decimal by another number with 2 places of decimal you get a number with 4
places of decimal as the result. Example: 12.34 *1.23 = 15.1782. If you
format these 3 numbers to show with 2 places of decimal, you'll see 12.34,
1.23 and 15.18. However the underlying value in the cell showing 15.18 is
still 15.1782. Formatting does not change underlying values, unless you
choose the set precision as displayed option.

 

[David Biddulph]

But wasn't the OP talking about adding, not multiplying?

 

[Wondering]

Yes but the OP did not specify how the numbers were derived. They are added
and formatted in accounting format with 2 places of decimal. The OP says
that cell Sheet2!D4 = Sheet1!C3 and that Sheet2!D4 is 381.9683. That means
that Sheet1!C3 must be 381.9683 and displays as 381.97 due to formatting.
Sheet1!C3 is the sum of Sheet1!C1 and Sheet1!C2 both of which are the sum of
two other cells. Somewhere along the way, 4 places of decimal were
introduced in these "other cells".

 

[Guest]

As clarification, all numbers are added or subtracted in the workbook. It is
a budget program with all input in two decimal entry form (nothing automatic
or fractional).

As I noted in an earlier reply, the problem does not replicate in a new
workbook.

The 'ROUND' work-around works (see above). The base cause may be related to
formatting or some such thing. With it not replicating (I should have checked
that first) I am not sure it is worth pursuing further.

I really do appreciate all the time and thinking being provided for this
event. Your efforts and rapid responses are heart-warming.

 

[Wondering]

You also have to realize that Excel keeps its numbers in floating point. For
many decimal numbers with decimal fractions there is no exact floating point
representation only approximations, so you will wind up with more than just
2 places of decimal just by doing adds and subtracts or by simply entering
numbers. You might think you have a number such as 77.10 when in fact it is
stored as 77.09999999999... and displays as 77.10 (formatted with 2 places
of decimal). You have to take this into account especially with dealing with
currency. See the following for correcting floating point rounding errors:
http://support.microsoft.com/kb/214118 Also Google IEEE 754 for much more
information on floating point numbers.

 

[Wondering]

See also an interesting article about floating point numbers at:
http://www.cpearson.com/excel/rounding.htm

 

[Wondering]

Here is a prime example of a floating point representation of decimal
numbers.



Format A1 thru A4 as numeric with 2 places of decimal.



Enter the following values: in A1, 68.6; in A2, 60. In cell A3 enter the
formula =A1-A2. In A4 enter 8.6 In A5 enter the formula: =A4=A3

Surprise! A5 displays FALSE. 68.6 - 60 equals 8.6 which does not equal 8.6!
How can this be? Expand range A1:A4 to 16 places of decimal.

A1 = 68.6000000000000000, A2 = 60.0000000000000000, A3 = 8.5999999999999900.
A4 =8.6000000000000000 A5 = FALSE



Now you can see that even additions (and subtractions) can produce
approximate answers.

 

[David Biddulph]

That is true, but of course it doesn't explain the magnitude of discrepancy
which the OP reported.

 

[Wondering]

Thats could easily be explained by a multiply having been done.

 

[David Biddulph]

Hence my earlier comment.
The OP still assures us that there's been nothing except addition and
subtraction, but he can't reproduce the problem on a new spreadsheet. It
may remain an unsolved mystery. :-(

 

[Wondering]

Perhaps someone accidentally entered a number with 3 or 4 places of decimal
in it.