• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match() Returning #N/A when matches exist [SOLVED]

Match() Returning #N/A when matches exist.
Column [C] calculates a difference, B-A
Column [D] looks for the result using Match()

[C] contains =B1-A1
[D] contains =MATCH(C1,$B$1:$B$6, 0)

A B C D
1 -47.69 1937.52 1985.21 2
2 601.77 1985.21 1383.44 3
3 601.77 1383.44 781.67 #N/A
4 124.7 781.67 656.97 #N/A
5 -40 656.97 696.97 6
6 -56.79 696.97

Why do rows 3 and 4, column [D], return "#N/A"?
 

Attachments

  • Book2.xlsx
    10.3 KB · Views: 6
Last edited by a moderator:
Hi AliGW I agree with the solution, what I do not understand is, if I subtract the "matching" pair, eg 781.67 in columns [C] and [D] and expand the result, I can find no residue that is, the result is 0.00 E00 no matter how many digits
 
Last edited by a moderator:
On another point for this post, my first, I did not know what to enter in the post submission fields, hence the odd "??? What do I do here" in the voting area.
 
I can't answer your second question - I have no idea how you managed to set this up as a vote! Maybe a ninja can help.

Regarding the issue with the sums, it's called a floating point error. Google it and you'll find out it's a pesky little issue that can rear its head in Excel - just being aware of it will help you to work round it when this sort of thing happens.
 
On another point for this post, my first, I did not know what to enter in the post submission fields, hence the odd "??? What do I do here" in the voting area.
Because you created a poll, while you just needed to create thread (first button above). You can leave "poll" empty.
upload_2018-8-13_9-58-49.png
 
Hi AliGW I agree with the solution, what I do not understand is, if I subtract the "matching" pair, eg 781.67 in columns [C] and [D] and expand the result, I can find no residue that is, the result is 0.00 E00 no matter how many digits
https://www.microsoft.com/en-us/mic...y-does-excel-give-me-seemingly-wrong-answers/

In your example:
781.67 is stored as 781.66998291015625
601.77 is stored as 601.77001953125
1383.44 is stored as 1383.43994140625

1383.43994140625 - 601.77001953125 = 781.669921875

781.66998291015625 is not equal to 781.669921875
 
Thanks Haz, I understand the effect, I just did not expect Excel to incorectly show 781.67 as 781.6700000000000000000000000000... when the decimal display is expanded. Nor did I expect when I took the difference of two numbers "the same" and again expanded I am presented with 0.0000000000000000000......
 
Last edited by a moderator:
Hi AliGW I agree with the solution, what I do not understand is, if I subtract the "matching" pair, eg 781.67 in columns [C] and [D] and expand the result, I can find no residue that is, the result is 0.00 E00 no matter how many digits
Hi AliGW, Thanks for your quick and correct response. I am not aware how to vote or close this "thread".
 

As this forum does not have a "closed" button,
you may edit the title of the original post …​
 
Hi Marc L, If I knew how to change the title or mark as closed I would.
Where is the option to do this?
 
Last edited by a moderator:
Back
Top