# Summing currency issue HELP!!

#### hhartensteiner

##### New Member
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]

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Welcome to the Board!

hhartensteiner said:
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,

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?

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.

Replies
0
Views
164
Replies
1
Views
240
Replies
3
Views
187
Replies
19
Views
2K
Replies
3
Views
221

1,203,060
Messages
6,053,305
Members
444,651
Latest member
markkuznetsov1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back