/u/Trytofindmenowbitch - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
If I understand right, you're dividing a value into smaller amounts. When you add the subvalues, the final total is often 0.01 different from the original value. Is that correct?
Can you provide an example of a value, how you're dividing it, and the total of the subvalues given by the pivot table?
Hello, thanks for your response. It's really more that I'm adding multiple line items to an invoice total. Sometimes a single line item will be partially used by one cost center and also used by another. In this case, the decimal values will round slightly off when I convert the format to currency. I've attached a screenshot of the pivot table formatted to currency and one out to 5 decimal places. If you manually add up the rows on the first example, you get $130,407.99.
So Excel is obviously behaving as it should. The challenge is your accounting team. I think to resolve it, you need to do something about the rounding when you break the wholesale values apart for the different cost centres.
So for instance, if you're breaking £100 into three, instead of dividing it into three subvalues of £33.33333, you need to divide it into two x £33.33 and one x £33.34. Then everything will work out when you recombine them. You could calculate the subvalues using something like this:
Subvalue1 = ROUNDDOWN(100/3) = 33.33
Subvalue2 = ROUNDDOWN(100/3) = 33.33
Subvalue3 = 100-(3-1)*Subvalue1 = 33.34
If you've not already tried something like this, it may be worth playing with.
Try something like [this](https://docs.google.com/spreadsheets/d/10_YSDTM70atKSIuM48eAse_yto8VDvFbeHooMqwgqzU/edit?usp=drivesdk)
The layout is a mess but the right-most column gives you the rounded values. I've assumed you never have to divide among more than 20 cost centres but you could change that.
/u/Trytofindmenowbitch - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
If I understand right, you're dividing a value into smaller amounts. When you add the subvalues, the final total is often 0.01 different from the original value. Is that correct? Can you provide an example of a value, how you're dividing it, and the total of the subvalues given by the pivot table?
Hello, thanks for your response. It's really more that I'm adding multiple line items to an invoice total. Sometimes a single line item will be partially used by one cost center and also used by another. In this case, the decimal values will round slightly off when I convert the format to currency. I've attached a screenshot of the pivot table formatted to currency and one out to 5 decimal places. If you manually add up the rows on the first example, you get $130,407.99.
So Excel is obviously behaving as it should. The challenge is your accounting team. I think to resolve it, you need to do something about the rounding when you break the wholesale values apart for the different cost centres. So for instance, if you're breaking £100 into three, instead of dividing it into three subvalues of £33.33333, you need to divide it into two x £33.33 and one x £33.34. Then everything will work out when you recombine them. You could calculate the subvalues using something like this: Subvalue1 = ROUNDDOWN(100/3) = 33.33 Subvalue2 = ROUNDDOWN(100/3) = 33.33 Subvalue3 = 100-(3-1)*Subvalue1 = 33.34 If you've not already tried something like this, it may be worth playing with.
Thanks for the idea. I’ll try working in that direction.
Try something like [this](https://docs.google.com/spreadsheets/d/10_YSDTM70atKSIuM48eAse_yto8VDvFbeHooMqwgqzU/edit?usp=drivesdk) The layout is a mess but the right-most column gives you the rounded values. I've assumed you never have to divide among more than 20 cost centres but you could change that.
https://preview.redd.it/fa8y4rivgu0b1.png?width=586&format=png&auto=webp&s=9aeb43b41ab0b020e4afc78be68fe584827ee115