Forum Discussion
Excel formula help
I hope someone can help.
I am working with a reconciliation sheet and I know there has to be a formula I can use to divide and each cell with the sum and give me the exact pennies. Right now my formula is simple
FORMULA: SUM=D3/C3
But since I am working with money I need the exact change and when I multiply all of the cells the pennies are over. I hope I made sense of this.
3 Replies
- Ok, I got that part, but the total does not add up correctly. I did an AUTOSUM and now im .03 off in pennies when I round. The total cost should be 8209.92 and I need to divide between 18 people. when I divide it the total comes out to 456.10666. If I put the formula SUM= D3/C3, and so on in each cell I would get the same total, but when I multiply 456.10 or .11 by 18 I would be off. I do these spreadsheets by just counting but I thought there would be a formula that I can use to simplify the process.
Since you are splitting an amount among several people and it doesn't divide evenly, the amount can't be the same for everyone. In those situations I usually have the same formula (like ROUND(D3/C3,2)) for everyone except one. I usually put the different formula at the top or bottom and take the total you want it to be (In your case 8,209.92) minus the total of all the other ones.
It will always add up but you will have arbitrarily have chosen one to be a few pennies different.
ROUND(D3/C3,2) will round your answer to 2 decimal places. If you don't round fractions of pennies will be there.
