Rounding error when using IF + STYLE on an array of percentages [closed]
Hello,
I'm trying to achieve something pretty simple: I have an array of percentages and I want to make sure they all sum up to 1 (100%).
In the cell after the end of my array I enter the following formula: SUM(my_array) + STYLE(IF(SUM(my_array) <> 1, "ERROR", "DEFAULT")).
Unfortunately my condition always evaluates to true, indeed using SUM on an array of floats will never give you an exact result, this is how computers work. What's the proper way to deal with that implementation detail? I have tried to define an epsilon constant with a very small value, and changed my predicate to ABS(SUM(my_array) - 1) > epsilon, but the condition always evaluates to false.
What's the proper way to do this?
-1- For what reason do you expect that a sum of 100% might occur with a reasonable probability?
-2- A desision about any tolerance to apply in a test fur "SUM = 100%" depends profoundly on the circumstances.
-3- There may also be reasons to judge a SUM to be "roughly equal 100%" if at least 97% while any excess is seen as absolutely intolerable.
-4- You need to define your limits depending on your needs.
The percentages in the array are entered manually and must all sum up to 100% (it's part of a resource allocation table), I'm trying to prevent against human error.
Just an optimization: instead of repeating the SUM in the IF parameter, you could use CURRENT() to re-use the already calculated sum value. No idea if Calc would optimize this itself otherwise.
If you manually enter values that should total to any specific sum, you need (in a sense) a cell accumulating the already made entries, or - better - to show the current lack/excess.
(Best do this in a frozen row above the entries.)