Excel Multiplication

  • Thread starter Thread starter Gerald Kraft
  • Start date Start date
G

Gerald Kraft

I just checked a simple worksheet where a number from one column multiplies
a number in a second column and the product is incorrect by a small amount.
Some of the products are correct, but many of them are in error. I don't
know how I can rely on the program at this time. Any ideas would be welcome
or a suggestion for another spreadsheet program that I could trust. I am
using Excel 2000 SP-3.
 
Hi Gerald

The most likey reason is the decimal place format of the cells. E.g
While you may see 1 the TRUE value could be 1.49 etc Just format the
cells in both columns to show 3 to4 decimal places.

Posted via: http://www.ozgrid.com
Excel Templates Training Add-ins.
Free Excel Forum & Business Software
 
I just checked a simple worksheet where a number from one column multiplies
a number in a second column and the product is incorrect by a small amount.
Some of the products are correct, but many of them are in error. I don't
know how I can rely on the program at this time. Any ideas would be welcome
or a suggestion for another spreadsheet program that I could trust. I am
using Excel 2000 SP-3.
Click to expand...

Perhaps you could give some examples. Other than small rounding errors in the
15th decimal place (which will be present in any spreadsheet that conforms to
IEEE standards), significant multiplication errors have not been reported.

Where the errors appear to be significant, the problem has usually been due to
a misunderstanding regarding rounding, display, and what's actually in the
cell.


--ron
 
You can see from the first entry (line 9) what is happening.

Entry Display
A9: 4007.634 4007.634
B9: 22.94 22.940
C9: 23 23.000
D9: =0.667*B9+C9*0.333 22.960
E9: =D9 22.960

If you were to format D9 as General, you would see that D9 displays
22.95998, not 22.96. But because you have the display set for 3 decimal
places, XL rounds *the display* to 22.960. The values stored in the cell
doesn't change - it's 22.95998.

If you select E9, you'll see in the Formula bar that the value is
22.95998. Again, your choosing to display with three digits after the
decimal points rounds the *display* to 22.960.

Therefore your calculations in F9:J9, based on your "Enter Value by
Hand" in E9, will have different results if the formula is based on D9,
with its value of 22.95988, or based on F9, with the hand-entered value
22.96.

Note that Excel will carry only 15 decimal digits of precision. It (like
every other spreadsheet) uses internal routines to minimize the error
below that.

There are a couple of ways to avoid or work around this problem.

First, you could choose Tools/Options/Calculation and check the
Precision as displayed checkbox. In that case, the value displayed in
the cell will be used as the value for subsequent calculations based on
that cell. Note that this can introduce errors if you're expecting
symmetric results. For instance, with the display set to three decimal
places:

A1: 1
A2: =A1/3 ==> 0.333
A3: =A1/3 ==> 0.333
A4: =A1/3 ==> 0.333
A5: =SUM(A2:A4) ==> 0.999

so the value in stored in A5 would actually differ from A1 by 0.001.

Second, you could use ROUND() to round to as many significant digits as
you'd like:

A5: =ROUND(SUM(A2:A4),3) ==> 1.000


For more, see

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

or

http://www.mcgimpsey.com/excel/pennyoff.html
 
As Ron said :-)

Take a close look at what the numbers in column E *really* are, as opposed to
the rounded number being displayed as a result of the formatting you have on the
data, and then compare that to the data you have typed into Column F. They are
not the same.

Example, D9 your formula appears to result in 22.960, and to 3 dps that is
correct, BUT, if you take a look at the value in E9 where you have copied and
pasted values, you will see in fact that that number is really 22.95998. You
have then manually entered 22.960 in column F, which is *not* the same, and
should therefore not be surprised that multiplying 2 different numbers by the
same factor results in different amounts.
 
Thanks guys. I guess in the past I wasn't working with such large numbers
where the rounding made such a difference. I am grateful. My fright is
over.

--
Gerald Kraft <[email protected]>
Ken Wright said:
As Ron said :-)

Take a close look at what the numbers in column E *really* are, as opposed to
the rounded number being displayed as a result of the formatting you have on the
data, and then compare that to the data you have typed into Column F. They are
not the same.

Example, D9 your formula appears to result in 22.960, and to 3 dps that is
correct, BUT, if you take a look at the value in E9 where you have copied and
pasted values, you will see in fact that that number is really 22.95998. You
have then manually entered 22.960 in column F, which is *not* the same, and
should therefore not be surprised that multiplying 2 different numbers by the
same factor results in different amounts.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission :-)
-------------------------------------------------------------------------- --



errors in
the conforms
to been
due to
Click to expand...
[/QUOTE]
 
Back
Top