Numbers changing between sheets

[Daan007]

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.

 

[OssieMac]

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

 

[Daan007]

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?

 

[Daan007]

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]

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.