Exact match with decimals doesn't work

**benishiryo** · 10-31-2012, 10:45 AM

hey guys. i was helping someone solve a problem awhile back & i guess it still bugs me why the MATCH formula couldn't work. despite having a work-around, i'm curious to know what went wrong.

let's simplify things & say i only want to lookup the decimal of this number in A1:

1.2

0.1	0.2	0.3

and i want to match it to the table below & return its position. so i do a formula:
=MATCH(MOD(A1,1),A3:C3,0)
or
=MATCH(A1-INT(A1),A3:C3,0)
both gives me #N/A when it's supposed to be 2. but keying the "0.2" would work:
=MATCH(0.2,A3:C3,0)

any ideas why?

**Miraun** · 10-31-2012, 10:48 AM

This may be a case of Excel Math Magic...

Excel is actually based on a form of binary math, IEEE 754, which is an international computational standard... Essentially it's functioning in a base 8 rather than a base 10 world. It makes for some really strange results when doing seemingly simple math, but it's also the reason that 10/3 doesn't end up in your CPU going through an endless loop. Essentially, it does calculations in base 8, and then messing with the math on the seemingly insignificant level (like, 10 points from the decimals that you'd normally be interested in!), in order to make it round numbers (most of the time).

Sadly, there's no real workaround, unless you go into Excel Options and toggle "Set Precision as Displayed" under Advanced > Formulas area. But there are other implications, as I'm sure you can clearly tell.

Alternatively, you can use ROUND functions around the seemingly simply stupid math to tell excel to cut it out with the additional billionth decimal point.

Give that a shot, and see if you continue to have the problems.

**Miraun** · 10-31-2012, 10:50 AM

Ohh... Looking at the way that you've set out the data, rather than using a match function, you could use a LOOKUP function to round it to the different columns you have identified... which you could then use in a V or H lookup, similarly to the last forum post that we both commented on.

**ajm123456789** · 10-31-2012, 10:52 AM

=MATCH(VALUE("0"&MID(A1,FIND(".", A1), (LEN(A1)-FIND(".", A1)+1))),A3:C3,0)

this seems to work

**benishiryo** · 10-31-2012, 11:05 AM

@Miraun: ahhhh i was afraid to hear of such an answer. it seems that someone has asked before since you managed to write an essay within 3 minutes haha. i guess i'll have to accept a work-around then. yes LOOKUP or VLOOKUP would work but i was curious why MATCH couldn't since both my formulas are exactly equals to 0.2

@ajm123456789: yeah i know there are work-arounds, as mentioned in my OP, but i'm just curious why this doesn't work

i'll mark it as "Solved" anyway. thanks guys

**dawsonh2002** · 05-08-2016, 12:34 PM

Not sure if this helps. Had to create a stop loss protection for our company to prevent paying out excessive profit sharing. When company-wide GP is less than 2.5%, we bank all profit share. Between 2.5% and 5%, we payout 50% and bank/hold the other 50% to pay out later. People receive their entire profit share (and any banked profit share) once company GP is above 5%. To accomplish this, you need to have at least two rows per value or range as shown below.

Set the table up in ascending order with the range of company GP. Left column contains formula =(Company GP Cell Reference)*10000. If you the GP were longer decimals (i.e. 2.49999%), you'd have to include a Round() statement to get rid of excess decimals.
Between -9999% to 2.49%, there should be a payout reduction of 100%.
Between 2.5% and 4.99%, we pay 50% of the persons profit share.
Greater than 5% GP, employees receive 100% profit share.

This is what the table needs to look like for index match to work:
Rounded format Company GP Payout Reduction (reverse, bank)
Column O Column P Column Q
-999900 -9999.00% 100.00%
249 2.49% 100.00%
250 2.50% 50.00%
499 4.99% 50.00%
500 5.00% 0.00%
99999900 999999.00% 0.00%

Setup another table as: (formulas could be combined to simplify)
Company GP 4.99%
Company GP Formatted 499
PS Reduction 50.00%
PS Bank 50.00%

Company GP (J11) 4.99%
Company GP Formatted (J12) =round(J11*10000,0)
PS Reduction (J13) =INDEX(Q:Q,match(J12,O:O,1))
PS Bank =1-J13

**dawsonh2002** · 05-08-2016, 12:35 PM

sorry for formatting. Not sure to have tabs in this forum.

**JohnTopley** · 05-08-2016, 12:42 PM

Can you start your own thread rather than "hijacking" an existing one already designated as SOLVED..

And it would help if you posted a sample file on the forum. To upload a file;

Click "Go Advanced" then scroll down to "Manage Attachments"

**dawsonh2002** · 05-08-2016, 01:10 PM

Sorry, was looking for an answer to this same issue. The provided solution wasn't what I was looking for, so I was sharing the solution that did solve the same problem, even though the thread was marked as "solved". You'll note, benishiryo noted, "i'll mark it as "Solved" anyway. thanks guys" meaning he probably wanted something else as well.

In any case, I wasn't Hijacking a thread that was solved, but providing a solution to an unsolved problem. I will not respond to another thread like this as requested. Thank you.

Thanks for tip on uploading files. That is huge help. My formatting sucked.

**JohnTopley** · 05-08-2016, 01:45 PM

Thanks for the feedback: now appreciate why you responded.

As you noted, uploading a file is the best (and probably) quickest way to get a response to any query, not least because it provides respondents with data to test any solution.

**FDibbins** · 05-09-2016, 12:35 AM

dawsonh2002 welcome to the forum

If you are answering a post, even if it is marked solved, you are more than welcome to. Posting your own question on someone elses thread (which you were not doing) is a no-no here

Exact match with decimals doesn't work

LinkBack

Thread Tools

Rate This Thread

Display

Exact match with decimals doesn't work

Re: Exact match with decimals doesn't work

Re: Exact match with decimals doesn't work

Re: Exact match with decimals doesn't work

Re: Exact match with decimals doesn't work

Re: Exact match with decimals doesn't work

Re: Exact match with decimals doesn't work

Re: Exact match with decimals doesn't work

Re: Exact match with decimals doesn't work

Re: Exact match with decimals doesn't work

Re: Exact match with decimals doesn't work

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions