Dec 2, 2024

Sheets won't do simple calculations correctly.

I am doing a task that requires finding the rate of change of certain values—in other words, the difference between Value 1 and Value 2. I was using the simple formula, =(J5-J4) to find the differences, but Sheets was actually calculating them wrong. I had 8 in J5 and 5 in J4, and when I plugged in the formula above, Sheets tried to convince me that the difference was 4! This continued for the rest of the values down the line, as seen in the attached screenshot.

I eventually found a workaround where I had Sheets give me the incorrect value, and then I subtracted 1 afterwards to compensate. This worked, but I am still very confused why such simple calculations were done wrong in the first place.

Details

Sheets,Microsoft Windows,Personal use

Locked

This question is locked and replying has been disabled.

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

Last edited Dec 2, 2024

All Replies (2)

Adam D. PE

Diamond Product Expert

Google Forum Volunteer - Not a Google employee

Dec 2, 2024

Hi Mr E Mann59,

There are a few reasons that may be causing this problem but without a sample file, it's not possible for a volunteer to look into the cause of the problem. We would like to help you with this but because there are so many variables that can affect your results, we need to see a sample of your problem. Would you please take a look at this post about Best Practices When Asking a Question and share a sheet that has the permissions set for “Anyone with link can EDIT”. For the forum volunteers to help you better, we need to see clear explanations and examples of what you need to occur. These examples do not require you to use formulas; just manually enter the data as it should appear. Thanks!

Ricky T 563

Dec 3, 2024

When you say you have "8" in a cell and "5" in another cell, for instance, my guess is that those aren't the actual raw numbers in the cells. Instead, you may have set the formatting for those cells to show whole numbers, while the actual underlying numbers are fractional. For instance, if you click on the cell that shows "8" and look in the formula bar up top, you may find that the underlying number is really 8.4; and if you click on the cell that shows "5" and look in the formula bar up top, you may find that the underlying number is really 4.8. Subtracting one cell from the other, then would result in 3.8, which your whole-number formatting would show as 4, not 3.

Formatting only changes what you see, not what the numbers really are. Your best bet, if you want to deal in real whole numbers is to wrap all formulas that show whole numbers in ROUND(). For example, if the formula that results in "8" (but really 8.4) is

=A2 * B2 / C2

then you'd change the formula to

=ROUND(A2 * B2 / C2)

If you do this for every cell that contains a formula somewhere in the chain that leads to your final result, then they should all show what you expect to see.