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.
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.
