If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
formula not calculating correctly
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% * |
#2
|
|||
|
|||
formula not calculating correctly
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 wrote in message ... 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% * |
#3
|
|||
|
|||
formula not calculating correctly
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 Cameron wrote: 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 wrote in message ... 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
|
|||
|
|||
formula not calculating correctly
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 Jerry W. Lewis wrote: 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 Cameron wrote: 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 wrote in message ... 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% * ... |
Thread Tools | |
Display Modes | |
|
|