I'm having some issues with FILTER() not returning consistent results, below is an image detailing the inconsistencies.

enter image description here

When filtering for 3:30 PM I only get 2 results instead of 3. When filtering for 11:00 PM I get 4 results out of 4.

Why is this?

Edit: Turns out the numbers are not exactly the same:

enter image description here

However, the odd part is, those numbers are originally pulled from here:

enter image description here

This makes all of it even more confusing, whats going on here?

  • Figured out where it's coming from, I have a form that is used to submit data on missed times..etc If you submit that form as 3:30 PM the number it submits is 0.645833333335759 and if you type out 3:30 PM in the spreadsheet, the number it uses is 0.645833333333333. I need to ask a new question. It does this with any time interval. – Douglas Gaskell Sep 28 '15 at 22:43

Timestamps are stored as floating point numbers in double precision, on the scale where 1 corresponds to a day. It is generally advisable not to test floating point numbers for equality; the rounding errors, inevitable in almost any calculation with them, may lead to unexpected results.

It appears that the transmission of time data from a form to the spreadsheet involved some sort of rounding of binary data along the way. Compare:

The number of zeros at the end of mantissa of the second number indicates that it was rounded along the way.

One solution is to use fuzzy comparison, for example

=filter(A76:A96, abs(A76:A96-B76) < 1e-9)

Since 10-9 days is less than 0.1ms, this matches the timestamps that are indistinguishable for practical purposes.

Another solution is to "fix" the distorted timestamp by converting the time to a string and back:

=timevalue(text(A92, "hh:mm:ss"))

This will return the result identical to other 3:30 PM entries of your sheet.

  • I did not consider floating point errors as being applicable in google docs in this form, since you should be able to directly compare two times (which are floats) without error. In this case, the erroneous numbers are being submitted from a google docs form. How does your function work? the Absolute Value of that would not be less than 0.000000001 and would return false no? – Douglas Gaskell Sep 28 '15 at 23:55
  • Absolute value of the difference is less than 1e-9. Two numbers entered directly into a sheet ought to be comparable, but receiving one of them from another source, with associated data format changes, makes comparison less certain. – user79865 Sep 28 '15 at 23:59
  • Ah! That makes sense, and it works! However, is there any way to just "convert" the bad number, to the correct one somewhere along the way? So I'm not fumbling with it throughout the entire spreadsheet? – Douglas Gaskell Sep 28 '15 at 23:59
  • Yes, I added this at the end of my answer. – user79865 Sep 29 '15 at 0:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.