number not recognised by Match function

**st!ngray** · 01-25-2013, 12:28 PM

hey guys, I ran into a little weird issue. So I have a formula that rounds a certain number to 0.10. And then an index match function will use that number again. The match function does not recognise the number though.

The value is 110.20 in cell B1. If I overwrite it with hardcoded 110.20, then the match function recognises it. I did a check by writing 110.20 in a different cell (A1), and then doing =A1=B1 with B1 being the formula value. And that says it is true. So there is no 110.1999999999999 going on or something like that.

When I round to 0.5 and to 0.25, there is nothing going wrong. Does someone have any clue what this could be?

**Moo the Dog** · 01-25-2013, 12:37 PM

Do you have a sample sheet for us to look at? Upload it to this thread. Usually when ROUND is involved it tends to be the reason the MATCH function acts weird, but we won't know until we see your work.

- Moo

**Tony Valko** · 01-25-2013, 12:38 PM

If B1 is the calculated value 110.20, try these test formulas EXACTLY as shown:

=(B1-110.20)=0

=(B1-110.20) Format this one as Number and take it out to about 30 decimal places.

**Pete_UK** · 01-25-2013, 12:39 PM

0.1 (and 0.2 therefore) does not have an exact representation in binary - it is a recurring decimal. Hence any way of representing it will have rounding errors, rather like 0.6666667 is not the same as 2/3 and neither is 0.66666666.

Hope this helps.

Pete

**MarvinP** · 01-25-2013, 12:42 PM

Pete wins the answer contest, IMHO.

Read http://www.cpearson.com/excel/rounding.htm for more information.

**Moo the Dog** · 01-25-2013, 12:49 PM

Originally Posted by MarvinP

Pete wins the answer contest, IMHO.

Is there a prize for that? LOL

- Moo

**Tony Valko** · 01-25-2013, 03:12 PM

If there is a floating point rounding error it will be revealed by the test formulas I suggested.

**MarvinP** · 01-25-2013, 04:40 PM

Hey Moo,

Great question. I read a lot of these answers and many times wonder how the answer relates to the question. When the answer doesn't tell which cell the formula goes in and it relative based, it makes me wonder if it is any help or not. If the OP should supply a workbook so we understand the answer, why shouldn't we be supplying a sample with our formulas in it? We might at least supply a link that explains what we mean or give a source to our answer.

I thought Pete's answer was a better example of what I'd like to have. I guess that makes him a winner in my book.

**st!ngray** · 01-28-2013, 04:58 AM

Wow, looks like I opened up a can of worms here :-) Please find attached a sample sheet.

I would say Pete and Marvin are the winners here for explaining the binary issue. Especially after I read the rounding article on cpearson.com.

Tony Valko's little formula proved the point though. =B1-110.20 did not give any value on the first 30 decimals. =(b1-110.20)=0 did give a FALSE though.

Any way I can work around this guys? Like multiplying and then divide in the end or so? I can change the match type but that feels wrong.

**Marcol** · 01-28-2013, 05:39 AM

Try this in D3, Drag/Fill Down.

Formula:

Please Login or Register  to view this content.

**st!ngray** · 01-28-2013, 05:57 AM

Perfect,thanks. It does not look pretty but hey it works.

number not recognised by Match function

LinkBack

Thread Tools

Rate This Thread

Display

number not recognised by Match function

Re: number not recognised by Match function

Re: number not recognised by Match function

Re: number not recognised by Match function

Re: number not recognised by Match function

Re: number not recognised by Match function

Re: number not recognised by Match function

Re: number not recognised by Match function

Re: number not recognised by Match function

Re: number not recognised by Match function

Re: number not recognised by Match function

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions