0

I have put two date time values in cells A1 and B1. The date time is precisely 41703.0416666667. One is ouput from an SQL database the other manually written.

The result of =if(A1=B1,1,0) is 1. The result of =MATCH(B1,A1,0) is #N/A.

Does anyone have any theories as to why this may be happening?

gstanley
  • 73
  • 2
  • 11
  • Are you sure both time values are exactly the same? If I copy `41703.0416666667` in two cells of a spreadsheet and do a match as you did, I do get 1. – nutsch Mar 05 '14 at 16:57
  • Hello, thanks for your reply. Suppose my explanations were not sufficient, when doing these steps 1 by 1 it does work. Perhaps I should have been more detailed and said that the contents in cell A1 are from the output of a mySQL database. – gstanley Mar 05 '14 at 17:11

2 Answers2

1

Probably an issue with converting decimal to binary. See related answer:

Simple HLOOKUP Failing with Excel 2010

This appears to be a limitation of storing floating point numbers in binary - as described here: http://support.microsoft.com/kb/214118

Many combinations of arithmetic operations on floating-point numbers in Microsoft Excel and Microsoft Works may produce results that appear to be incorrect by very small amounts. For example, the equation =1*(.5-.4-.1) may be evaluated to the quantity (-2.78E-17), or -0.0000000000000000278 instead of 0.

This problem is not unique to excel either but rather a result of:

IEEE 754 specifies that numbers be stored in binary format to reduce storage requirements and allow the built-in binary arithmetic instructions that are available on all microprocessors to process the data in a relatively rapid fashion. However, some numbers that are simple, nonrepeating decimal numbers are converted into repeating binary numbers that cannot be stored with perfect accuracy.

Community
  • 1
  • 1
Stepan1010
  • 3,136
  • 1
  • 16
  • 21
  • Agreed with Stepan, another source on the floating-point issue: http://support.microsoft.com/kb/78113 – nutsch Mar 05 '14 at 17:14
  • Agreed this is more then likely the problem as the match correctly occurs at 41703.375 however is there a work around for this? – gstanley Mar 05 '14 at 17:28
  • @gstanley For purposes of comparing equality or doing LOOKUP-type functions I would think you could just convert them to long integers.(so no decimals). If you have other plans for using the date/time and you absolutely need that level of accuracy then there are ways to minimize the risk inaccuracy but they are not going to eliminate the accuracy problems: http://en.wikipedia.org/wiki/Floating_point#Minimizing_the_effect_of_accuracy_problems - also see: http://stackoverflow.com/questions/14859875/c-how-to-avoid-floating-point-arithmetic-error – Stepan1010 Mar 05 '14 at 17:52
0

The issue is with the floating-point calculation (see http://support.microsoft.com/kb/78113), one possible workaround is to work with the round() function. In your case, rounding to 10 or 12 decimals would probably be enough to address the issue.

nutsch
  • 5,922
  • 2
  • 20
  • 35
  • Hello, thanks for the information. I have already tried with round() to 10 decimals. It has not resolved the issue. When I copy paste the values of the cells to a note pad both result in the exact same number. – gstanley Mar 05 '14 at 17:20