Match worksheet Function displays wrong data on calculation
 

I am looking for some assistance on a way to get around what I believe
is an error in excel of some sort.

I am attempting to do a Match based off of a calculated field as the
data in my worksheet is in percentages and the data in another
worksheet is in whole numbers.

Cell: N35 = 1.7500%
Cell: Q36 = =MATCH(N35*100, CRF!E56:E67, -1)

I need this to be a -1 as I want to show the largest number if there
is no exact value that is closest to the original number.

Sheet Match:
Cell E56: 3
Cell E57: 2.75
Cell E58: 2.5
Cell E59: 2.25
Cell E60: 2
Cell E61: 1.75
Cell E62: 1.5
Cell E63: 1.25
Cell E64: 1
Cell E65: .75
Cell E66: .5
Cell E67: .25

It works properly for every single number except 1.75 and I can't
figure out why. It keeps displaying Row 5 which correlates to 2.

If I change Q36 to be =MATCH(1.75, CRF!E56:E67, -1) it works fine, but
when I do (1.7500%*100) it doesn't which leads me to believe that
excel is viewing those as two different numbers. I have tried using
round to just bring it to 4 digits, I even tried displaying 30 digits
to see if there was some sort of calculation bug within excel.

Please help.

Match worksheet Function displays wrong data on calculation
 

On Sep 17, 12:48*pm, MSJ wrote:
I am looking for some assistance on a way to get around what I believe
is an error in excel of some sort.

I am attempting to do aMatchbased off of a calculated field as thedatain myworksheetis in percentages and thedatain anotherworksheetis in whole numbers.

Cell: N35 = 1.7500%
Cell: Q36 = *=MATCH(N35*100, CRF!E56:E67, -1)

I need this to be a -1 as I want to show the largest number if there
is no exact value that is closest to the original number.

SheetMatch:
Cell E56: 3
Cell E57: 2.75
Cell E58: 2.5
Cell E59: 2.25
Cell E60: 2
Cell E61: 1.75
Cell E62: 1.5
Cell E63: 1.25
Cell E64: 1
Cell E65: .75
Cell E66: .5
Cell E67: .25

It works properly for every single number except 1.75 and I can't
figure out why. *It keeps displaying Row 5 which correlates to 2.

If I change Q36 to be =MATCH(1.75, CRF!E56:E67, -1) it works fine, but
when I do (1.7500%*100) it doesn't which leads me to believe that
excel is viewing those as two different numbers. *I have tried using
round to just bring it to 4 digits, I even tried displaying 30 digits
to see if there was some sort ofcalculationbug within excel.

Please help.

Okay I found a solution myself, but I would still like to get a better
understanding of why the error is occuring. I have now changed the
match formula to =MATCH(Trunc(N35*100,4), CRF!E56:E67, -1) and it
works fine. However, the number in that field is really 1.75% and it
should not need to be trucated to match.