Detect and correct result using a tolerance

English
#1

I have some data that started with an integer with repeating addition that should end up with an integer again, eg 1 + 0.1 + 0.1 + … that should end up with …, 1.9, 2, 2.1, …, 3, 3.1, … For some reason, sometimes those expected integers end up with a very small number, which I’ve needed data so small, eg #E-15, while I seldomly (if ever) needed to go as log as #E-5. Is there a way for when there’s a number so small, a tolerance, that the number is rounded and the background color is set to something very pale, eg when the number is expected to be 1.5 but it’s instead 1.500000000006, it’s set to 1.5 and the background becomes Light Yellow 4 ? I want it rounded as I don’t want the minute number to keep repeating.

I’m sorry of the topic was already brought up and solved but I didn’t find it in my search.

Thank you kindly for your help

#2

This Wikipedia page has a good explanation of the effect, Numeric precision in Microsoft Excel - Wikipedia

I would suggest to use the round function in your formula to prevent the binary conversion errors accumulating

#3

Specifically, there is no 0.1 in Calc (nor in Excel). When you type 0.1, the number that program uses is exactly 0.1000000000000000055511151231257827021181583404541015625.

See also this FAQ.

#4

If you look at my example there’s a good number of 0s less than the number you gave, I think 5. I don’t plan on repeating the operation 10,000 times so couldn’t my example work if there was a way to do so? It was an example, if the real number was only 9 0s, then I could just set the rounding at 5 0s if I knew how to do it.

#5

Do you mean every-time the formula reaches an integer you start the formula again, incrementing by 1, so the minute number repeatedly added resets? Any tip how to do it?

#6

Sorry, I can’t decipher this phrase. What is “a good number of 0s” and what is less than what… I only pointed to the root cause of the problem: a user can expect that what they enter is what the program uses, but in the area of decimals, this is not the case most of the time.

You can use a formula using ROUNDSIG, either in the cell itself (to drop the insignificant parts), or in a conditional format (to check if the result of the function is equal to the value in the cell). Usually the function would decrease the imprecision of the result; but you should never assume that what you see in the cell (e.g., 0.1) is strictly what is there (even if Calc would never show you the decimals beyond 15 significant ones).

#7

You have received the tip already.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.