Summing currency issue HELP!!

[hhartensteiner]

HELP!! I have a problem I can't figure out! I have a worksheet with the following:

Balance of Funding = $314,500.00 which should equal the TOTAL.
A column labeled Percentage
A column labeled Dollar Amount
A Contingency amount of $22,550.00

The percentage column adds up to 100%
The dollar amount column totals $314,500.00

The formula for the dollar amount column is
Balance of Funding - contingency * Percentage = Dollar Amount
There are 38 items adding up to the total so I won't list them.

My problem is this. Two of the items in the dollar amount column are calculating with 3 decimal points which can't be paid in currency. They are both calculating to 1/2 a cent. Because of this the TOTAL is correct but I can't pay out in 1/2 cent increments. Is there anything I can do?

I know this is a bad explaination so if I can clear something up please let me know. I really need to get this fixed and FAST!

Thanks!
Heather[/b]

 

[Barrie Davidson]

Welcome to the Board!

The formula for the dollar amount column is
Balance of Funding - contingency * Percentage = Dollar Amount
There are 38 items adding up to the total so I won't list them.

My problem is this. Two of the items in the dollar amount column are calculating with 3 decimal points which can't be paid in currency. They are both calculating to 1/2 a cent. Because of this the TOTAL is correct but I can't pay out in 1/2 cent increments. Is there anything I can do?

Would rounding your calculation to two decimal places solve the problem? Changing your calculation from:
Balance of Funding - contingency * Percentage
to:
=Round((Balance of Funding - contingency)*Percentage,2)

Hope this helps,

 

[hhartensteiner]

Summing currency issue HELP!

Thank you Barrie!
This is causing my total to be increased now by $0.01. The total is correct already. What I need to happen is the two items in my dollar amount column to round off automatically so that the TOTAL equals the Balance for Funding. Does this make any sense?

 

[Barrie Davidson]

It makes sense, but you probably won't like my answer; can you use a "rounding" account to hold that $0.01? That's what I do when encountering rounding errors. The formula in that cell (assuming your data is in A2:A400) would be:

="Balance for Funding"-SUM(A2:A400)

If you can't use this approach, you're going to have to manually add (or subtract) in a cell (or cells) to arrive at your total.