[Solved] Zero value causing error with IF function?

[Mark312]

Hi, this is driving me a little crazy, hope someone can help. Apologies if this is long winded, I've tried to include everything relevant.

I put together a spreadsheet using LibreOffice, to help us reconcile our till at the end of the day. One of the most critical cells is to show if the till is up or down, so I wrote an IF formula in row 25, which looks at the result of row 24. My formula is:

=IF(G24<0,"DOWN",IF(G24>0,"UP"))

which I have set up to achieve the following:

Condition 1: If the cell in row 24 has a negative value it shows the word "Down" (till is down)
Condition 2: If the cell in row 24 has a positive value it shows the word "UP" (till is up)
Otherwise the cell is left blank.

In the first week of entering values everything was fine until Friday, when row 24 showed a zero value in red (cells are formatted to show negative values in red) and row 25 showed the word UP. The till is balancing, but the formula thinks that it isn't. Clearly a zero value shouldn't produce an UP/DOWN result.

This is what I have tried so far:

Re-wrote it from scratch - same result.
Re-wrote it from scratch on a different pc running Apache - same result.
Tried entering the values from Friday into any other column and it does the same on that day's column.

I don't think my formula is at fault (please correct me if I'm wrong). The problem only occurs when row 24 produces a zero figure that wants to be displayed as zero. (I have the spreadsheet set not to display zero values in the Tool>Options>Calc>View page.)

I have attached the sheet exactly as it appeared on the Friday.

Does anyone have any clues? I would be very grateful for your help.

Till sheet for forum help.ods

(21.24 KiB) Downloaded 125 times

[FJCC]

G24 is not exactly zero. It has a tiny value, 2E-13, due to the limited precision of digital calculations. Try using the formula

Code: Select all

=IF(ROUND(G24;2)<0;"DOWN";IF(ROUND(G24;2)>0;"UP"))

so that only two decimal places of precision are used in the comparison with zero.

[keme]

What FJCC said...

This happens, as FJCC wrote, because Calc cannot represent every decimal fraction exactly. When several numbers with a slight offset are added, this kind of tiny rounding error shows up.

Perhaps even better than changing the G25 balance indicator, adjust the formula in G24 using that rounding function:

Code: Select all

=ROUND(G23-G22;2)

This clears both alerts when the till is balanced.

[Mark312]

Thank you!

That seems to have fixed it.

I can't understand how a SUM function with values only entered to 2dp can produce a figure with more than 2dp.

[Zizi64]

I can't understand how a SUM function with values only entered to 2dp can produce a figure with more than 2dp.

The value 0.1 seems a simple decimal number in the decimal numeric system. But the computers work in the binary numeric system with the powers of the 2 (the fractions have negative: 2E-1 = 1/2 = 0.5, 2E-2 = 1/4 = 0.25 ...).
0.1 (dec) = 1/2 +1/4 + 1/8 + 1/16 + ... +1/1024 + ... + 1/65536 + ... + ... : this is an infinity binary fraction number. This value will be rounded after an arithmetic operation, and all of the roundings will cause a little error.

See it in your modified example file:

Till sheet for forum help.ods

(21.22 KiB) Downloaded 79 times

[Mark312]

Thank you - that is extremely helpful.

I will start using ROUND on more of my spreadsheets in future!