Ask Your Question
0

Using VALUE function to compare two numbers yields incorrect results

asked 2021-05-01 00:25:27 +0200

johnnyquest gravatar image

updated 2021-05-02 23:47:33 +0200

Added example file on 2021-0502: JQ C:\fakepath\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.

edit retag flag offensive close merge delete

Comments

What is the solution, if any?

Who should analyze this using the list?
Give us an odt. File with incorrect results.

PKG gravatar imagePKG ( 2021-05-01 08:46:37 +0200 )edit

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

Earnest Al gravatar imageEarnest Al ( 2021-05-01 09:03:16 +0200 )edit
1

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.

Earnest Al gravatar imageEarnest Al ( 2021-05-01 13:02:48 +0200 )edit

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

johnnyquest gravatar imagejohnnyquest ( 2021-05-01 20:45:10 +0200 )edit

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.

Earnest Al gravatar imageEarnest Al ( 2021-05-01 22:58:49 +0200 )edit
1

Post an example of the file.

Schiavinatto gravatar imageSchiavinatto ( 2021-05-02 00:41:03 +0200 )edit

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 ...(more)

LeroyG gravatar imageLeroyG ( 2021-05-02 03:07:08 +0200 )edit

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

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

johnnyquest gravatar imagejohnnyquest ( 2021-05-02 06:34:08 +0200 )edit

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.

Earnest Al gravatar imageEarnest Al ( 2021-05-02 07:10:46 +0200 )edit

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

johnnyquest gravatar imagejohnnyquest ( 2021-05-02 23:47:53 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2021-05-03 14:43:26 +0200

erAck gravatar image
edit flag offensive delete link more

Comments

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

sokol92 gravatar imagesokol92 ( 2021-05-03 15:16:33 +0200 )edit
Login/Signup to Answer

Question Tools

3 followers

Stats

Asked: 2021-05-01 00:25:27 +0200

Seen: 69 times

Last updated: May 03