debits do not = credits due to rounding when using % to calculate

[JBoyer]

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 834"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]See below example. D671 = 41,298. 41,298 x %. All % = 100% but when the result hits the debit or credit column, debits do not = credits by a few cents. Any suggestions?

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JE AMT[/TD]
[TD]DEBIT[/TD]
[TD]CREDIT[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 834"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]-41298[/TD]
[TD]=IF(D671>0,D671,0)[/TD]
[TD]=IF(D671<0,-D671,0)[/TD]
[/TR]
[TR]
[TD]=ROUND(-$D$671*VLOOKUP(A672,'Premium YTD results'!A:J,10,FALSE),2)[/TD]
[TD]=IF(D672>0,D672,0)[/TD]
[TD]=IF(D672<0,-D672,0)[/TD]
[/TR]
[TR]
[TD]=ROUND(-$D$671*VLOOKUP(A673,'Premium YTD results'!A:J,10,FALSE),2)[/TD]
[TD]=IF(D673>0,D673,0)[/TD]
[TD]=IF(D673<0,-D673,0)[/TD]
[/TR]
[TR]
[TD]=ROUND(-$D$671*VLOOKUP(A674,'Premium YTD results'!A:J,10,FALSE),2)[/TD]
[TD]=IF(D674>0,D674,0)[/TD]
[TD]=IF(D674<0,-D674,0)[/TD]
[/TR]
[TR]
[TD]=ROUND(-$D$671*VLOOKUP(A675,'Premium YTD results'!A:J,10,FALSE),2)[/TD]
[TD]=IF(D675>0,D675,0)[/TD]
[TD]=IF(D675<0,-D675,0)[/TD]
[/TR]
[TR]
[TD]=ROUND(-$D$671*VLOOKUP(A676,'Premium YTD results'!A:J,10,FALSE),2)[/TD]
[TD]=IF(D676>0,D676,0)[/TD]
[TD]=IF(D676<0,-D676,0)[/TD]
[/TR]
[TR]
[TD]=ROUND(-$D$671*VLOOKUP(A677,'Premium YTD results'!A:J,10,FALSE),2)[/TD]
[TD]=IF(D677>0,D677,0)[/TD]
[TD]=IF(D677<0,-D677,0)[/TD]
[/TR]
[TR]
[TD]=ROUND(-$D$671*VLOOKUP(A678,'Premium YTD results'!A:J,10,FALSE),2)[/TD]
[TD]=IF(D678>0,D678,0)[/TD]
[TD]=IF(D678<0,-D678,0)[/TD]
[/TR]
[TR]
[TD]=ROUND(-$D$671*VLOOKUP(A679,'Premium YTD results'!A:J,10,FALSE),2)[/TD]
[TD]=IF(D679>0,D679,0)[/TD]
[TD]=IF(D679<0,-D679,0)[/TD]
[/TR]
[TR]
[TD]=ROUND(-$D$671*VLOOKUP(A680,'Premium YTD results'!A:J,10,FALSE),2)[/TD]
[TD]=IF(D680>0,D680,0)[/TD]
[TD]=IF(D680<0,-D680,0)[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

A separate column where you can trace the differences?

 

[joeu2004]

D671 = 41,298. 41,298 x %. All % = 100% but when the result hits the debit or credit column, debits do not = credits by a few cents. Any suggestions?

Not sure I understand. I certainly cannot relate that statement to the formulas that you posted.

For me, it would be better if you uploaded an example Excel file that demonstrates the problem to a file-sharing website, and post the download URL in a response here. I like box.net/files. Please verify that the download URL works when you are logged into the file-sharing website.

(Caveat: Some contributors object because they cannot or will not download external files. But often, the devil is in details that are difficult to duplicate by copying data and formulas into postings.)

Some general observations, perhaps misdirections....

Percentage calculations rarely sum to the whole. Percentages rarely sum to exactly 100%. Rounding calculations often cause "quantization errors"; again, the sum of the parts is not equal to the whole.

In many professional financial reports, this is dealt with by a footnote to the effect "sums might not agree due to rounding".

There are strategies that try to "distribute" the quantization errors. None is perfect; so I prefer the KISS approach. I could offer a general paradigm. But it would be better to fit it to your example Excel file, if possible.