2

I have this worksheet where I need to create a checker to determine whether a number (result of dividing the sum of two numbers by another value --DIVISOR) is an integer/does not have decimals. Upon running the said checker, it mostly worked just fine but appeared to detect that a few items are not integers despite being exact multiples of the DIVISOR.

https://docs.google.com/spreadsheets/d/17-idS5G0kUI7JoHAx3qcJOiJ-zofmMrg93hUvZuxPiA/edit#gid=0

Screenshot of Google Sheet file

I have two values (V1 and V2) whose sum I need to divide by a certain number (Divisor).

I need the OUTPUT to be an integer/whole number. Since the DIVISOR is a multiple of SUM (V1,V2), the OUTPUT is supposed to be a whole number. I also expanded the number of decimal places to make sure that there are no trailing numbers after the decimal point.

However, upon running the MOD function over the OUTPUT, it generated some infinitesimal value.

I also tried TRUNCATING the OUTPUT and getting the DIFFERENCE between the TRUNC and OUTPUT. It yielded the same remainder value as the MOD result.

I downloaded the GSheet and opened it in MS Excel. There seems to be no problem with the DIFFERENCE result, but the MOD function yielded yet another value.

Screenshot of worksheet downloaded as an Excel file

player0
  • 124,011
  • 12
  • 67
  • 124
qzxrt
  • 23
  • 4
  • Neither `983386.8` nor `9933.2` can be represented as finite binary expansions, so most programs will round them and introduce some error (except those programs that have an option to use (much slower) decimal representations). Does Google Sheets have an option to mark a cell as a decimal value? Is rounding values that are supposed to be integers to integers an option? Is there a maximum denominator you can multiply every value by (e.g. if you're working with money, can you choose to work with integer numbers of cents rather than fractional numbers of dollars)? – HTNW Nov 23 '22 at 07:19
  • the sample spreadsheet is not open for access. – Ping Nov 23 '22 at 07:26
  • @Ping Thanks for the flag. I edited the permission so it could be accessed by anyone. – qzxrt Nov 23 '22 at 07:58
  • @HTNW Thanks for the response. I'm still trying to digest your answer so please correct me if there's any misinterpretation. FIRST, related to finite binary expansions, I included another example in the link above where GSheet managed to generate the expected result. In my worksheet, I have 172 rows over which I ran the checker. Of those, only 7 had infinitesimal remainders despite being exact multiples of the DIVISOR. I can't seem to find any pattern in these 7 instances. – qzxrt Nov 23 '22 at 08:16
  • @HTNW SECOND, not sure if this is what you mean, but I am using the default 'Number' format in GSheet for the cells in question. I also tried using the 'Financial' format but the results were the same. I tried Googling "mark cell as a decimal in GSheet" but I can't seem to find any relevant answers aside from the number formatting I mentioned above. THIRD, unfortunately, rounding values is not an option since I expect all values to be a multiple of a certain constant to check whether there were miscalculations or typo errors in data inputs. – qzxrt Nov 23 '22 at 08:24
  • @HTNW FOURTH (1/2), V1 and V2 in the GSheet link are actually monetary values. The constant/divisor (760) is the price per unit. The reason why there is a breakdown (99% and 1%) is that there is an amount withheld for every payment, which is equal to 1% of the total fee. Hence, what I actually have as a primary input is the 99%. Then I derive the 1% retention fee (secondary data) using the primary input. I then divide the sum of the primary input and secondary data by the price per unit (760). This way I can flag the resulting values with decimals as I expect them to be integers. – qzxrt Nov 23 '22 at 08:35
  • @HTNW FOURTH (2/2) I tried separately processing the WHOLE NUMBER portion from the DECIMAL portion of the values as suggested and I actually eliminated the infinitesimal remainder. However, in the counter-example (with correct results) I've given in the GSheet link, it generated an infinitesimal remainder by using the disaggregated process. Here's a screenshot for more clarity: https://imgur.com/a/e104sSb – qzxrt Nov 23 '22 at 09:00

1 Answers1

2

actually, this is not a bug and it is pretty common. its called a floating point "error" and in a nutshell, it has to do things with how decimal numbers are stored within a google sheets (even excel or any other app)

more details can be found here: https://en.wikipedia.org/wiki/IEEE_754

to counter it you will need to introduce rounding like:

=ROUND(SUM(A1:A))

this is not an ideal solution for all cases so depending on your requirements you may need to use these instead of ROUND:

ROUNDUP
ROUNDDOWN
TRUNC
TEXT
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Thank you for the Wiki reference. I am so dumbfounded right now that a function as simple as ROUND would solve my problem. This was actually suggested by @HTNW but it was rounding values to integers. Instead, I will just round the numbers up to two or three decimal places. I think this would work for now since I noticed that all infinitesimal remainder values were to the magnitude of 10^-11. Thank you so much for your help. – qzxrt Nov 23 '22 at 09:58
  • @qzxrt more (non-GS) reading material if needed: https://stackoverflow.com/questions/21895756 & https://stackoverflow.com/questions/588004 – player0 Nov 23 '22 at 10:18
  • 1
    wow thank you for sharing! these are some really useful readings. cheers! – qzxrt Nov 24 '22 at 08:53