-1

Can someone please tell me how this could possibly result in an error???

enter image description here

I don't know about you, but that looks like an exact match to me...

4
  • 1
    Looks can be deceiving. You're comparing floating point numbers. They match as far as the displayed digits go, but times tend to be imprecise in the decimal places not displayed (and are stored as imprecise numbers). You will need to incorporate some form of rounding or truncation to ensure an exact match. – fixer1234 Jun 19 '16 at 6:09
  • I could be wrong, but should the second parameter be an array and not a value? – Jedi Jun 19 '16 at 6:12
  • @Jedi, You're right. I missed the forest for the trees. Why don't you do the honors and post an answer? – fixer1234 Jun 19 '16 at 6:17
  • @Yes, I voted to put your question on hold until you provide the information needed for people to help. As I explained in a comment on the answer below, the question is misleading as written (the equation shown is not what's in your formula), and is not answerable without the actual formula and referenced cell values. Questions that can be answered only by speculation don't meet the site's requirements, and it's unfair to have other readers waste time preparing and cluttering the site with answers that aren't useful. – fixer1234 Jun 27 '16 at 3:46
3

The second parameter should be an array and not a value. That's what caused the error message.

However, you also are not likely to get the right answer if you fix only that. Floating point numbers are imprecise, so trying to do an exact match on something like time values may not match even if the displayed digits are the same.

4
  • @fixer1234, the second parameter IS an array, it only shows the matching value that it found. Also, the numbers originate from the exact same sources and are not altered, I increased the decimal places about 15 "0's" after the final digit and they are 100% match. I've even tested both resulting values in separate cells and set them equal to each other and set the dates and times that they originated from equal to each other and excel tells me that they are all exactly equal. I have a much larger function that this is taken from and I found this part to be the cause of the functions' failure. – Yes Jun 27 '16 at 3:04
  • And again, they resulted from simple dates and times. The formula adds up a date cell and a time cell which are both set equal to data validation cells on another sheet, then it finds a match in a range that results from adding multiple dates and times. One of the values in the range comes directly from adding up the data validation cells. Originally, I had the values coming from different places and I wasn't sure how, for example, 6/1/2016 1:00:00 pm could not be equal to itself, since the date/time format is the same. To try to remedy it, I just made the values branch from the same cells. – Yes Jun 27 '16 at 3:13
  • @Yes, we're running into the same problem here as your related question. You're asking people to figure out what the problem is, but you've provided zero information that can be used for diagnosis. The image in your question is misleading, because it isn't displaying what's in your formula. You haven't even identified the specific error message. If you're getting an error and/or wrong answer, you're doing something Excel doesn't like. But there is no way for anyone to replicate your problem without the actual formula and referenced cell contents. – fixer1234 Jun 27 '16 at 3:24
  • @Yes, BTW, 1:00:00PM, converted to time is 13/24 of a day, or 0.541 followed by an infinite number of 6's. You can enter all the decimals you want, but that value won't be an exact match for 1:00:00PM. That's why if you want to do exact matching, you need to do it against values that have been rounded or truncated, or otherwise converted to a form you can compare to exactly. – fixer1234 Jun 27 '16 at 3:32

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.