multiplication problem

[FM]

I recent found a problem and need help to solve.

If I enter the the data in the following columns, using the example entries
I get different answers:

M1 M2 M3
24 2.18 =M1*M2 results in 52.22

24 2.18 =24*2.18 results in 52.32

The 52.32 is the correct answer needed.

Thanks
FM

[Neil]

What version of Excel are you running?

I tried this on Excel 2002 with SP3, and everything worked fine!

Neil
www.nwarwick.co.uk

"FM" wrote:

> I recent found a problem and need help to solve.
>
> If I enter the the data in the following columns, using the example entries
> I get different answers:
>
> M1 M2 M3
> 24 2.18 =M1*M2 results in 52.22
>
> 24 2.18 =24*2.18 results in 52.32
>
> The 52.32 is the correct answer needed.
>
> Thanks
> FM
>
>
>

[JE McGimpsey]

Did you enter these by hand, or are they the result of a formula?

If the latter, try increasing the number of decimal points. You'll find
that either M1 or M2 are slightly less than their displayed values, but
they are rounded to 0 or 2 decimal places by the display engine. As a
workaround, try enclosing the M2 function in a ROUND(<function>,2)
formula, for example.

In article <#[email protected]>,
"FM" <[email protected]> wrote:

> I recent found a problem and need help to solve.
>
> If I enter the the data in the following columns, using the example entries
> I get different answers:
>
> M1 M2 M3
> 24 2.18 =M1*M2 results in 52.22
>
> 24 2.18 =24*2.18 results in 52.32
>
> The 52.32 is the correct answer needed.

[Ron Rosenfeld]

On Sat, 16 Apr 2005 22:05:11 -0700, "FM" <[email protected]> wrote:

>I recent found a problem and need help to solve.
>
>If I enter the the data in the following columns, using the example entries
>I get different answers:
>
>M1 M2 M3
>24 2.18 =M1*M2 results in 52.22
>
>24 2.18 =24*2.18 results in 52.32
>
>The 52.32 is the correct answer needed.
>
>Thanks
>FM
>

It is likely that the contents of M1 and/or M2 is not exactly 24 and 2.18
respectively, but that the display is rounded to those values.

When you are obtaining the above, what do the following formulas give for
results?

1. =M1=24
2. =M2=2.18

What do M1 and M2 look like if you Format/Cells/Number Decimal Places:15


--ron

[FM]

Maybe I didn't explain the situation correctly, here goes:

First example entry:
Cell location is M (column) : 1 (row 1) Manually input 24
Cell location is M (column): 2 (row 2) Manually input 2.18
Cell location is M (column): 3 (row 3) Manually input formula: =24*2.18
(result = 52.32)

Second example entry:
Cell location is M (column) : 1 (row 1) Manually input 24
Cell location is M (column): 2 (row 2) Manually input 2.18
Cell location is M (column): 3 (row 3) Manually input formula: =M1*M2
(result = 52.22)

In the either example entry I get the same result no matter how many decimal
points I enter. If there is a round off functioning here I'm at a loss as to
how it would come up with a smaller number?

I have windows 98se with Office 2000 premium addition. If I enter the
formula via the cell copy method or input the formula: =(M1*M2) in brackets
the result is the same. In looking at previous work It would appear that
this is a recent problem.
Thanks
FM

[Ron Rosenfeld]

On Sun, 17 Apr 2005 20:13:29 -0700, "FM" <[email protected]> wrote:

>Maybe I didn't explain the situation correctly, here goes:
>
>First example entry:
>Cell location is M (column) : 1 (row 1) Manually input 24
>Cell location is M (column): 2 (row 2) Manually input 2.18
>Cell location is M (column): 3 (row 3) Manually input formula: =24*2.18
>(result = 52.32)
>
>Second example entry:
>Cell location is M (column) : 1 (row 1) Manually input 24
>Cell location is M (column): 2 (row 2) Manually input 2.18
>Cell location is M (column): 3 (row 3) Manually input formula: =M1*M2
>(result = 52.22)
>
>In the either example entry I get the same result no matter how many decimal
>points I enter. If there is a round off functioning here I'm at a loss as to
>how it would come up with a smaller number?
>
>I have windows 98se with Office 2000 premium addition. If I enter the
>formula via the cell copy method or input the formula: =(M1*M2) in brackets
>the result is the same. In looking at previous work It would appear that
>this is a recent problem.
>Thanks
>FM
>

I cannot reproduce the results you get.

Is it on a particular worksheet/workbook?
Does it happen with a brand new workbook?
--ron

[JE McGimpsey]

If you want to zip the file and send it to me from your posting address
I'd be interested in taking a look. AFAIK, what you're describing can't
happen...


In article <#[email protected]>,
"FM" <[email protected]> wrote:

> I have windows 98se with Office 2000 premium addition. If I enter the
> formula via the cell copy method or input the formula: =(M1*M2) in brackets
> the result is the same. In looking at previous work It would appear that
> this is a recent problem.

[Jerry W. Lewis]

I join the chorus that cannot reproduce (tried 2 versions), and does not
believe that this can happen exactly as I understand you to have described.

Works correctly in Office 2000 under Win 2000 Professional
Works correctly in Office XP under Win Me

If the wrong answer is not reflective of formulas in M1:M2, perhaps the
M3 formula pre-existed and M1:M2 was changed with
Tools|Options|Calculation set to Manual?

Jerry

FM wrote:

> Maybe I didn't explain the situation correctly, here goes:
>
> First example entry:
> Cell location is M (column) : 1 (row 1) Manually input 24
> Cell location is M (column): 2 (row 2) Manually input 2.18
> Cell location is M (column): 3 (row 3) Manually input formula: =24*2.18
> (result = 52.32)
>
> Second example entry:
> Cell location is M (column) : 1 (row 1) Manually input 24
> Cell location is M (column): 2 (row 2) Manually input 2.18
> Cell location is M (column): 3 (row 3) Manually input formula: =M1*M2
> (result = 52.22)
>
> In the either example entry I get the same result no matter how many decimal
> points I enter. If there is a round off functioning here I'm at a loss as to
> how it would come up with a smaller number?
>
> I have windows 98se with Office 2000 premium addition. If I enter the
> formula via the cell copy method or input the formula: =(M1*M2) in brackets
> the result is the same. In looking at previous work It would appear that
> this is a recent problem.
> Thanks
> FM

[FM]

Dear Jerry,

I looked at the Tools, Options and the Calculations are set at automatic.

I decided that It could be a percentage usage so tried it again in the
following:
First Manual input----

Starting Balance $16,207.33
A B C D
E F G
Days Payment 4.90% 365 Interest
Principal Balance
24 $485.99 $794.16 $2.18 $52.32
$433.67 $15,773.66
input
24 $485.99 16207.33x4.9% 794.16/365 2.18*24
485.99-52.32 16207.33-433.67

Second Formual input---

ROW A B C D
E F G
10 Days Payment 4.9% 365 Interest
Principal 16207.33
11 24 $485.99 $794.16 $2.18 $52.22
$433.77 $15,773.56

input
11 24 $485.99 G10*C10 C11/D10 A11*D11
B11-E11 G10-F11

The problem is E11. Even if you round down I don't think you can get this
number. I 've tried it in new books and get the same result. Either the
provram is corrupted or I've somehow set something inadvertently wrong?

Thanks to all for looking at the problem.

Fm

[Jerry W. Lewis]

As all respondants previously suggested, the issue is the difference
between the contents of D11 (2.17577854794521) and a formatted display
(2.18) that only shows part of what is in the cell.

52.32 =24*2.18
52.22 =24*2.17577854794521

You can get 52.32 by =A11*ROUND(D11,2) or by going to
Tools|Options|Calculation and checking "Precision as displayed". The
second option applies to all calculations, and may have unwanted side
effects.

Jerry

FM wrote:

> Dear Jerry,
>
> I looked at the Tools, Options and the Calculations are set at automatic.
>
> I decided that It could be a percentage usage so tried it again in the
> following:
> First Manual input----
>
> Starting Balance $16,207.33
> A B C D
> E F G
> Days Payment 4.90% 365 Interest
> Principal Balance
> 24 $485.99 $794.16 $2.18 $52.32
> $433.67 $15,773.66
> input
> 24 $485.99 16207.33x4.9% 794.16/365 2.18*24
> 485.99-52.32 16207.33-433.67
>
> Second Formual input---
>
> ROW A B C D
> E F G
> 10 Days Payment 4.9% 365 Interest
> Principal 16207.33
> 11 24 $485.99 $794.16 $2.18 $52.22
> $433.77 $15,773.56
>
> input
> 11 24 $485.99 G10*C10 C11/D10 A11*D11
> B11-E11 G10-F11
>
> The problem is E11. Even if you round down I don't think you can get this
> number. I 've tried it in new books and get the same result. Either the
> provram is corrupted or I've somehow set something inadvertently wrong?
>
> Thanks to all for looking at the problem.
>
> Fm
>
>
>
>
>
>
>
>

[FM]

Jerry,
Thank you and all the respondants. Your help was much appreciated and I'm
back on track again.

Sincerely,
FM

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:42678F18.5040003@no_e-mail.com...
> As all respondants previously suggested, the issue is the difference
> between the contents of D11 (2.17577854794521) and a formatted display
> (2.18) that only shows part of what is in the cell.
>
> 52.32 =24*2.18
> 52.22 =24*2.17577854794521
>
> You can get 52.32 by =A11*ROUND(D11,2) or by going to
> Tools|Options|Calculation and checking "Precision as displayed". The
> second option applies to all calculations, and may have unwanted side
> effects.
>
> Jerry
>