A strange problem

  • Thread starter Thread starter Pro
  • Start date Start date
P

Pro

Hi,

I'm using Excel 2000 to make a calculation for the following.

Column C =B/A
The result show at the last row which using sum(range) and the column C at last row is also using B/A.
It is very strange that the result showing 8.52242 instead of 7.XXXXXX.
Is it a calculation error or bug in Excel?


A
645 B
5000 C
7.75194
177 1375 7.76836
23 176.6 7.67826
1000 7800 7.80000
309 2388.7 7.73042
822 6353.8 7.72968
745 5781.6 7.76054
321.5 2501 7.77916
1180.5 9142.6 7.74468
1698 13135.4 7.73581
1000 7807.4 7.80740
1082 8368.9 7.73466
375 2900.1 7.73360
648.1 5028.9 7.75945
515 4000 7.76699
465.5 3596.9 7.72696
3146.89 24545.74 7.80000
14153.49 109902.6 7.76506
-14838.1 -115737 7.80000

-684.62
-5834.62
8.52242
 
Excel correctly calculates
(-5834.62)/(-684.62) = 8.52242...
but your 18th row of "numbers" (14153.49 and 109902.6) are text, not
numbers, and therefore were ignored by the SUM() function even though
they were coerced into numbers by the division operator.

You can easily recognize the problem by removing all horizontal
formatting; by default numbers are right justified and text is left
justified. Alternately, you can apply the COUNT() function to the range
and you will see that what it recognizes as numbers is less than what
you intended.

To convert text to numbers, you either must pre-format the cell to
receive a number and then re-enter it, or copy a cell containing a
numeric zero and Edit|Paste Special|Add over the cell or cells that need
to be converted.

Jerry
 
Attachments to newsgroups are strongly discouraged, and few people will
risk opening them.

Excel's arithmetic is presumably done by the math coprocessor in the
Pentium chip, rather than MS re-inventing the wheel. I have been using
Excel for a number of years, and have yet to see any evidence of math
errors (though there were issues with pre SR-2 Excel 97 failing to
recalculate when it should). In particular, as I noted previously, the
calculation (-5834.62)/(-684.62) = 8.52242... is correct, but -5834.62
is not the sum of the 19 preceding numbers in column B, and -684.62 is
not the sum of the 19 preceding numbers in column A. What formulas are
returning these values? When I use SUM() function, as you said that you
did, I get different values that are consistent across both Excel 2000
and Excel XP.

Jerry
 
Sorry for attaching the file.

A18 is the total of A1 to A17 and -684.82 is comming from SUM(A18:A20)
B18 is the total of B1 to B17 and -5834.62 i.e.B22 is comming from
SUM(B18:B20)

I find the problem is in column C that is the ratio, comming from e.g.
B1/A1, are between 7.6xxx and 7.8xxx.
But the ratio of the result,i.e. B22/A22, is 8.52242.
It is a very funny result.



The -684.62 is the different from
 
Then the first 17 rows are irrelevant. What you have reduces to

14,153.49 109,902.64 7.76506
-14,838.11 -115,737.26 7.80000
-684.62 -5,834.62 8.52242

You can easily independently verify that all of these results are
correct, and therefore neither a calculation error nor a bug in Excel.

If your question is not about Excel, but about the math, and why the
ratio of the sum is not between the two ratios, then look at it
algebraically. Denote the first two rows by

a1 r*a1 r
a2 (r+d)*a2 r+d

the sum then is (with a3=a1+a2)

a3 r*a3+d*a2 r+d*a2/a3

If d=0 (if the first two ratios are identical) then the third ratio
would also be r.

If a1 and a2 had the same sign, then a2/a3 would be less than one, and
the third ratio would be between the first two.

Since a1 and a2 differ in sign with a3 having the same sign as a2 you
necessarily have ABS(a3)<ABS(a2) so that the third ratio cannot be
between the first two.

Jerry
 
Back
Top