formula not calculating correctly

[Guest]

The formula in the third column is =L8/M8. I have the
third column formatted as Percentage with no decimal
positions. As you can see the cells with an astrik
beside them are not calculating correctly. Any Ideas?

Mike


14 14 100%
0 0 0%
8 8 100%
2 2 98% *
4 7 57%
1 1 95% *
1 1 95% *
10 11 91%
7 7 99% *
2 2 98% *
14 16 87% *
6 7 85% *
7 7 99% *
4 5 79% *
4 4 99% *
1 2 49% *
1 1 95% *
3 3 98% *

 

[Cameron]

Try this instead ...

=IF(ISERROR(SUM(L8/M8)),0,SUM(L8/M8))
and copy down.
the 0 is for the case of #DIV/0 errors.

Hope that helps.

Cameron

 

[Jerry W. Lewis]

SUM is not necessary in your formula.

You are performing the same calculation as the OP, and hence would get
the same results that he thinks are in error.

To get for example
2 2 98%
one or both of the numbers in columns L and M are not exactly 2.
Formatting to hide decimal places does not change the underlying value
and therefore does not change the calculation (unless you check
"Precision as displayed" at Tools|Options).

If you want to ignore the decimal places without using "Precision as
displayed", try

=IF(ROUND(M8,0)=0,0,ROUND(L8,0)/ROUND(M8,0))

Jerry

Try this instead ...

=IF(ISERROR(SUM(L8/M8)),0,SUM(L8/M8))
and copy down.
the 0 is for the case of #DIV/0 errors.

Hope that helps.

Cameron

....

 

[Jerry W. Lewis]

You should also evaluate whether your calculation is meaningful in the
presence of this rounding. Without restriction on the process
generating these numbers, 2 could be anywhere from 1.5 to 2.5, so the
percetage calculated from 2 2 could be anywhere from 60% to 1.67%

Jerry