Pivot Table Rounding Limitations / Solutions

bontindle

New Member
Joined
Jul 20, 2016
Messages
1
I have a sheet that takes several items and after it calculates, info gets pulled over into a pivot table for easy breakdown for billing. The problem is that the pivot table rounds when it receives the data, regardless of whether I want it to or not. This causes an occasional penny to be allocated wrong. That seems small, but a penny off throws our accounts payable process into a tailspin.

The problem I have is the data (mileage) and currency both go out to two decimal places, but the math is going out the third or fourth decimal place. My pivot table lists amounts and when I grab them with the mouse and highlight them, my total might be $100.00 everywhere that I double check my math. But when I manually type out the numbers and highlight them to see the total, it's $100.01. Or $99.99.

We are looking at the prospect of hard coding a solution that basically makes a table manually (the user won't feel the difference) but subtotaling it the way I want can be difficult because we do it across several different criteria. A pivot table was made to do this perfectly, why it no worky? I have set the data inside the Value Field Settings for the table and it didn't help (and reset itself often).

I'm not asking for code snippets necessarily, just your general knowledge to help me understand so I don't make this harder than I have to. I've read about this limitation elsewhere and wonder if it's true that there is no way to make a pivot table stop rounding. If so, I hope there is a workaround that I've missed. I've tried truncating, rounding, and every applicable math function to see if I can get them to agree. I have set the number beyond the decimal place and it still rounds behind the scenes.



Thank you all for your help. I use this site ALL THE TIME for guidance.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Watch MrExcel Video

Forum statistics

Threads
1,132,780
Messages
5,655,246
Members
418,183
Latest member
skaufman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top