Using VALUE function to compare two numbers yields incorrect results

English
, ,
#1

Added example file on 2021-0502: JQ
value_test.ods

Hello:
Currently, I am running Libreoffice CALC Version: 5.1.6.2 - Build ID: 1:5.1.6~rc2-0ubuntu1~xenial10.

The issue is this: I have two columns of numbers, which are text fields, not numerical fields. I use the following formula to compare the values of each: =IF(VALUE(E1)=VALUE(G1),“Okay”,“Error”). In most ALL case, the result returns “Error”, which is incorrect. In some cases, the result returns “Okay”, which IS correct.

Here’s a paste of several rows of actual data:

[  Col E    Col F          Col G ]

$235.68	Okay	$235.68

$225.73	Okay	$225.73

$215.94	Okay	$215.94

$212.94	Okay	$212.94

$195.64	Error	$195.64

$187.84	Error	$187.84

$170.26	Error	$170.26

$168.27	Error	$168.27

I’ve seen this problem over the past few releases of LIBRECALC for the past five years. It appears to be a bug that has yet to be reported (I’ve searched for the past few years) nor fixed.

What is the solution, if any?

Thanks in advance for any constructive input.

Peace and blessings.

#2

What is the solution, if any?

Who should analyze this using the list?

Give us an odt. File with incorrect results.

#3

Probably a difference in the digits below cents. Try rounding then comparing

#4

Try =IF(ROUND(E1;2)=ROUND(G1;2);"OK";"Error")

If the figures are in a currency format then you will only see to cents accuracy but the underlying figures could well have many more digits of accuracy. Adding GST, Tax, VAT, etc can easily create fractions of cents.

#5

Interesting. I spent some time going though all 1800+ rows. When I entered an actual number instead of a formula for column E in certain rows, it corrected the errors for many more rows below the correction. I made probably a dozen hand-correction entries to clear up all the errors.

I went back with your suggested formula and applied it to many rows as a test and it corrected the problem. What doesn’t make sense is that column G is simply text entries and the only other formula in the spreadsheet other that the one I posted is to add/subtract the value from the row above it. So where the extraneous decimals below the cents value came from is somewhat of a mystery. I’ll look a bit deeper into the issue now that I have a solution.

Thank you for the suggestion.

Peace and blessings,
JQ

#6

Adding 1800 rows you could run into the limitations of floating point accuracy. I’m not competent to explain but a quick search on internet found this explanation, for Excel but the principal is the same.

#7

Post an example of the file.

#8

EDIT: Having seen the sample file, editing and reposting as comment.

If you copy from a currency formatted cell…

  • and Paste (Ctrl+V) into a text formatted cell, the target cells format would change to currency.
  • and Paste Special (insert or Ctrl+Shift+V) using the default options (Text, Numbers, Date & Time) into a text formatted cell, cell content will be currency but aligned as text, unless you later edit the content of the cell.
  • and Paste Unformatted Text (Ctrl+Alt+Shift+V) into a text formatted cell, cell content will convert to text (this is the valid option here).

Choosing menu View - Value Highlighting (or Ctrl+F8) you can see if cells content are formatted as text (it will be displayed in black) or number (it will be displayed in blue).

Also, if the content of the current cell is a number, you could see in the status bar the Sum: value. If cell content is number as text, Sum: will show 0.

#9

I have an example file prepared with notes but how do I attach a file? There seems to be no way to do so. :frowning:

Okay, so I found a free, no-frills service to store the example file: [https://file.io/16jdlJwnixzh]

Is there a way to add a file to a commet?

Peace and blessings,
JQ

#10

To add a file, click edit just below your question, use the paperclip icon to upload the file then Save. You can upload ODF formats, e.g. .odt, .ods . If you need add another file type, e.g. .zip, just rename the file extension before uploading.

#11

Example file now attached to the question.
Thank you. JQ

#12

Interesting. When changing the value in cell A75 it generates an error !!!

And it appears in the cell =-$#REF!.#REF!50

and even if it comes back the error doesn’t go away,

#13

Changing the text to numbers and formatting as currency allows changing the formulas to remove Value() which just adds complexity. As a bonus, “Error” reduces from 207 to 123

Remove Apostrophe in Calc

  1. Select the column in which the
    digits are found in text format.
  2. Choose Edit > Find & Replace (Ctrl+H).
  3. In the Search for box, enter ^.
  4. In the Replace with box, enter &
  5. Check Regular expressions.
  6. Check Current selection only.
  7. Click Replace All.

The small errors still exist but don’t accumulate and seem relatively harmless unless you look for them

#14

Changing the formula to use ROUND instead of VALUE cures the “match” column issue (column C in the example file). This was your suggestion earlier. Though it doesn’t explain all the oddities (as noted in the example file) of CALC adding non-existent fractional decimals beyond 2 digits in the formula in column B. I have an EASY answer but if this question exposes long-standing bugs in CALC that the developers can/wish to fix then great! I sure would like to comprehend the oddities though.

Thanks again.
Peace and blessings,
JQ

#15

FAQ

#16

First “Error” found at row 756 and then—after F9—in row 757. Tested with LibreOffice 6.4.7.2/7.0.4.2/7.1.2.2 (x86); OS: Windows 6.1.

My opinion is that @EarnestAl’s first comment has the answer (given the new info: =IF(ROUND(VALUE(B15);2)=ROUND(VALUE(D15);2),"Okay","Error")).

#17

https://erack.de/bookmarks/D.html#010203

#18

… and this applies not only to Calc: Floating-Point Expressions Do Not Compare as Equal. :slight_smile:

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