Feb 23, 2021

Pie Chart Data Label Summation issue

My Pie chart is summing the dollars spent in various categories, and displaying that total as the data label. As such, there is only ever 2 decimal places in the data (cents). However, google sheets consistently has some weird error in summation where it ends up adding or subtracting .000000000001 from the total of some a category. I'm assuming this is some kind of floating point error, and it's really not a problem for the data but it makes my charts look awful (see below).

Is there any way to either avoid this issue, or format the chart so it only displays 2 decimal point at most?

Thank you!

Details

Sheets,Microsoft Windows,Personal use

Locked

Informational notification.

This question is locked and replying has been disabled.

Community content may not be verified or up-to-date. Learn more.

Recommended Answer

Gill K

Diamond Product Expert

Feb 24, 2021

Hi Sage,

I'm checking with some of the experts to see if I can find what causes that rounding error in the AGGREGATE charting function, or if there is a way to force the format to two decimal places.

A possible workaround would be to calculate the aggregate (sum) values as a separate small table, from your existing data, and then chart that. Is that possible?

Cheers,

Gill

Original Poster Sage Bauer marked this as an answer

Helpful?

All Replies (5)

Gill K

Diamond Product Expert

Feb 24, 2021

Hi Sage,

I believe that the pie chart takes its formatting from the actual data, and can't be controlled easily otherwise. Have you tried changing the column format, where you are getting your data from, to show only two decimal places?

You can test it by formatting just the cell that has the 724.06.0000000001 value in it.

You'll see in the next image that when I format column A to display one decimal place, the chart only shows one decimal place, even though the data value is 233.05050506, as seen in the formula bar.

You might also need to use a custom number format on that column, if you don't want two decimal places showing for whole integer values.

Let me know if this helps.

Gill

When you've received a response that answers your question, please observe these forum courtesies;

• Leave your demo sheet shared as part of this solution's archive,

• Click Recommend on the post that best addressed your question, and

• Post again soon!

Sage Bauer

Original Poster

Feb 24, 2021

Hi Gill,

I was actually able to reproduce the issue in an example worksheet, attached here: https://docs.google.com/spreadsheets/d/1tBtTUzNcFT5zyn4vXK68BLWF_M6Qi_QXK3yeq0vCb18/edit?usp=sharing

Here is a screenshot of the full thing:

The column is set to "Currency" with 2 decimal places.

Gill K

Diamond Product Expert