Google-sheets – Google Sheets value on chart off from source by very small fraction

google sheets

Trying to figure out why Google Sheets chart is off from the source data by a small fraction. My sheet has 7 rows of data and a sum row. All data only has 2 decimal points and the sum obviously has 2 decimal points. Yet when plotted, the sum is off as shown in the screenshot below.

The callout on the chart is pulling its data from cell C12. Formula for C12 is just SUM(C4:C10). I expanded the decimal places in C12 to show that nothing was hidden/rounded/truncated. Trying to understand why this is happening and how to stop it.

Best Answer

I can't reproduce it, but this looks like a well-known (at least among programmers) peculiarity in floating point arithmetic. You can see in the graph that the internal representation of the number is different from what you'd expect.

The only solution I see is to decrease the number of decimals in C1, e.g. with this button in the toolbar:

Related Solutions

Google-sheets – Create Google chart from dynamic number of sheets

You can by using the ImportRange() function, and then reference the first cell. You will just need to reference every sheet that you need the information from.

Example:

Column A                  Column B     Column C

unique Spreadsheet key    A1           =importRange(A1,b1)
unique Spreadsheet key    A1           =importRange(A1,b1)

You will also need permissions from every sheet that you want to import from.

https://support.google.com/docs/answer/3093340?hl=en

It might be better to look at another way to output the data, maybe look to save it in a database.

Formatting DIVIDE Results as Unsimplified Fractions in Google Sheets

Try this instead of DIVIDE,

=QUERY(O4/M10,"select Col1 format Col1 '# ?/"&M10&"'")

M10:Denominator
O4:Numerator
By dynamically formatting Denominator to always be M10 through QUERY,We preserve the underlying number as well as the format(as opposed to TEXT ,Where we cannot change the underlying real value.)

Best Answer

Related Solutions

Google-sheets – Create Google chart from dynamic number of sheets

Formatting DIVIDE Results as Unsimplified Fractions in Google Sheets

Related Topic