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.

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" wrote:

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.

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.

"OssieMac" wrote:

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" wrote:

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.

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.


"Daan007" wrote in message
...
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.

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

"Wondering" wrote in message
. net...
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.

"Daan007" wrote in message
...
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.

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".

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
But wasn't the OP talking about adding, not multiplying?
--
David Biddulph

"Wondering" wrote in message
. net...
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.

"Daan007" wrote in message
...
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.

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" wrote:

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".

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
But wasn't the OP talking about adding, not multiplying?
--
David Biddulph

"Wondering" wrote in message
. net...
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.

"Daan007" wrote in message
...
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.

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.




"Daan007" wrote in message
...
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" wrote:

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".

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
But wasn't the OP talking about adding, not multiplying?
--
David Biddulph

"Wondering" wrote in message
. net...
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.

"Daan007" wrote in message
...
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.

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

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.



"Daan007" wrote in message
...
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.

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

"Wondering" wrote in message
t...
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.

"Daan007" wrote in message
...
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.

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
That is true, but of course it doesn't explain the magnitude of
discrepancy which the OP reported.
--
David Biddulph

"Wondering" wrote in message
t...
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.

"Daan007" wrote in message
...
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.

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. :-(
--
David Biddulph

"Wondering" wrote in message
t...
Thats could easily be explained by a multiply having been done.

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
That is true, but of course it doesn't explain the magnitude of
discrepancy which the OP reported.

"Wondering" wrote in message
t...
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.

"Daan007" wrote in message
...
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.

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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. :-(
--
David Biddulph

"Wondering" wrote in message
t...
Thats could easily be explained by a multiply having been done.

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
That is true, but of course it doesn't explain the magnitude of
discrepancy which the OP reported.

"Wondering" wrote in message
t...
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.

"Daan007" wrote in message
...
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.