In column A I have a list of numbers from 1 to 300.
In column B I have =100/A1 dragged down.
In column C I have = ROUND(B1,0)/100

In column E I have numbers starting at 0.01, and increasing by 0.01.
In column F I have =MATCH(A1,$C$1:$C$300,0)

Some of these are errors, I don't have the first clue why.
0.05 gives the answer 19, so on row 19 I can see that, 100/19 is 5.26316, I
can see that this is rounded to 5, then when divided by 100 is now 0.05, and
this if the first instance of 0.05 in column C. Thats perfect.
0.06 gives an error though. But I can see that on row 16, 100/16 = 6.25000,
which when rounded and divided by 100 is now 0.06. There are 3 rows where the
answer is 0.06 (16, 17, 18) but match can't seem to find any of them?
Similarly, I can see that there are errors for 0.10 to 0.16, where I can
clearly see in column C that the numbers 0.14, 0.13, 0.11 and 0.10 are
present.

I tried changing it to a vlookup (although I need it as a match) and the
errors were still aparantly. I've tried changing the match type in the
formula to -1, this give me an answer for each one, but quite often the wrong
answer...

I've done a formula of =E6=C16 to see if both 0.06's are the same, and it
returns as TRUE, so why can't MATCH find it?

I think your MATCH is working perfectly!!

Here is what I get for the first 10 rows:

1 100 1.0000000 0.01 1
2 50 0.5000000 0.01 #N/A
3 33.33333333 0.3300000 0.01 #N/A
4 25 0.2500000 0.01 #N/A
5 20 0.2000000 0.01 #N/A
6 16.66666667 0.1700000 0.01 #N/A
7 14.28571429 0.1400000 0.01 #N/A
8 12.5 0.1300000 0.01 #N/A
9 11.11111111 0.1100000 0.01 #N/A
10 10 0.1000000 0.01 #N/A

All of column C except C1 is correctly #N/A because, with the exception of
the first row, all the values in column A are greater than 1 and all the
values in column C are less than 1. If match_type is 0, MATCH finds the
first value that is exactly equal to lookup_value. Lookup_array can be in any
order.




Column D is blank and column E has no effect on column F

--
Gary''s Student


"PaulW" wrote:

In column A I have a list of numbers from 1 to 300.
In column B I have =100/A1 dragged down.
In column C I have = ROUND(B1,0)/100

In column E I have numbers starting at 0.01, and increasing by 0.01.
In column F I have =MATCH(A1,$C$1:$C$300,0)

Some of these are errors, I don't have the first clue why.
0.05 gives the answer 19, so on row 19 I can see that, 100/19 is 5.26316, I
can see that this is rounded to 5, then when divided by 100 is now 0.05, and
this if the first instance of 0.05 in column C. Thats perfect.
0.06 gives an error though. But I can see that on row 16, 100/16 = 6.25000,
which when rounded and divided by 100 is now 0.06. There are 3 rows where the
answer is 0.06 (16, 17, 18) but match can't seem to find any of them?
Similarly, I can see that there are errors for 0.10 to 0.16, where I can
clearly see in column C that the numbers 0.14, 0.13, 0.11 and 0.10 are
present.

I tried changing it to a vlookup (although I need it as a match) and the
errors were still aparantly. I've tried changing the match type in the
formula to -1, this give me an answer for each one, but quite often the wrong
answer...

I've done a formula of =E6=C16 to see if both 0.06's are the same, and it
returns as TRUE, so why can't MATCH find it?

I knew I'd get something wrong, column F should be
=MATCH(E1,C$1:$C$300,0)

Also, column E goes up by 0.01 each time.
In Your example (only the first 10 rows) they should be all errors apart
from the last row, where it searches for 0.10 (E10) and find it in row 10 of
column C.
If you continue it down to row 20 you'll get an answer for 0.05, and if you
keep going to row 30 you'll get an answer for as little as 0.03.

You can see in the below example that theres an error for 0.06, but it can
be planely seen in column C. Even row 10 is an error...

1 100.00 1.00 0.01 #N/A
2 50.00 0.50 0.02 #N/A
3 33.33 0.33 0.03 29
4 25.00 0.25 0.04 23
5 20.00 0.20 0.05 19
6 16.67 0.17 0.06 #N/A
7 14.29 0.14 0.07 14
8 12.50 0.13 0.08 12
9 11.11 0.11 0.09 11
10 10.00 0.10 0.10 #N/A
11 9.09 0.09 0.11 #N/A
12 8.33 0.08 0.12 #N/A
13 7.69 0.08 0.13 #N/A
14 7.14 0.07 0.14 #N/A
15 6.67 0.07 0.15 #N/A
16 6.25 0.06 0.16 #N/A
17 5.88 0.06 0.17 6
18 5.56 0.06 0.18 #N/A
19 5.26 0.05 0.19 #N/A
20 5.00 0.05 0.20 #N/A
21 4.76 0.05 0.21 #N/A
22 4.55 0.05 0.22 #N/A
23 4.35 0.04 0.23 #N/A
24 4.17 0.04 0.24 #N/A
25 4.00 0.04 0.25 #N/A
26 3.85 0.04 0.26 #N/A
27 3.70 0.04 0.27 #N/A
28 3.57 0.04 0.28 #N/A
29 3.45 0.03 0.29 #N/A
30 3.33 0.03 0.30 #N/A


"Gary''s Student" wrote:

I think your MATCH is working perfectly!!

Here is what I get for the first 10 rows:

1 100 1.0000000 0.01 1
2 50 0.5000000 0.01 #N/A
3 33.33333333 0.3300000 0.01 #N/A
4 25 0.2500000 0.01 #N/A
5 20 0.2000000 0.01 #N/A
6 16.66666667 0.1700000 0.01 #N/A
7 14.28571429 0.1400000 0.01 #N/A
8 12.5 0.1300000 0.01 #N/A
9 11.11111111 0.1100000 0.01 #N/A
10 10 0.1000000 0.01 #N/A

All of column C except C1 is correctly #N/A because, with the exception of
the first row, all the values in column A are greater than 1 and all the
values in column C are less than 1. If match_type is 0, MATCH finds the
first value that is exactly equal to lookup_value. Lookup_array can be in any
order.




Column D is blank and column E has no effect on column F

--
Gary''s Student


"PaulW" wrote:

In column A I have a list of numbers from 1 to 300.
In column B I have =100/A1 dragged down.
In column C I have = ROUND(B1,0)/100

In column E I have numbers starting at 0.01, and increasing by 0.01.
In column F I have =MATCH(A1,$C$1:$C$300,0)

Some of these are errors, I don't have the first clue why.
0.05 gives the answer 19, so on row 19 I can see that, 100/19 is 5.26316, I
can see that this is rounded to 5, then when divided by 100 is now 0.05, and
this if the first instance of 0.05 in column C. Thats perfect.
0.06 gives an error though. But I can see that on row 16, 100/16 = 6.25000,
which when rounded and divided by 100 is now 0.06. There are 3 rows where the
answer is 0.06 (16, 17, 18) but match can't seem to find any of them?
Similarly, I can see that there are errors for 0.10 to 0.16, where I can
clearly see in column C that the numbers 0.14, 0.13, 0.11 and 0.10 are
present.

I tried changing it to a vlookup (although I need it as a match) and the
errors were still aparantly. I've tried changing the match type in the
formula to -1, this give me an answer for each one, but quite often the wrong
answer...

I've done a formula of =E6=C16 to see if both 0.06's are the same, and it
returns as TRUE, so why can't MATCH find it?

if you used the drag down method on column E to generate ther numbers, they
may not be exact. try goint to column F and entering
=round(E1,2)
and drag down.
use columm F as you match and try it.

If you expand your digits in Column E you will probably start seeing noise
at the 13th or 14th digit which makes it not match.

"PaulW" wrote:

I knew I'd get something wrong, column F should be
=MATCH(E1,C$1:$C$300,0)

Also, column E goes up by 0.01 each time.
In Your example (only the first 10 rows) they should be all errors apart
from the last row, where it searches for 0.10 (E10) and find it in row 10 of
column C.
If you continue it down to row 20 you'll get an answer for 0.05, and if you
keep going to row 30 you'll get an answer for as little as 0.03.

You can see in the below example that theres an error for 0.06, but it can
be planely seen in column C. Even row 10 is an error...

1 100.00 1.00 0.01 #N/A
2 50.00 0.50 0.02 #N/A
3 33.33 0.33 0.03 29
4 25.00 0.25 0.04 23
5 20.00 0.20 0.05 19
6 16.67 0.17 0.06 #N/A
7 14.29 0.14 0.07 14
8 12.50 0.13 0.08 12
9 11.11 0.11 0.09 11
10 10.00 0.10 0.10 #N/A
11 9.09 0.09 0.11 #N/A
12 8.33 0.08 0.12 #N/A
13 7.69 0.08 0.13 #N/A
14 7.14 0.07 0.14 #N/A
15 6.67 0.07 0.15 #N/A
16 6.25 0.06 0.16 #N/A
17 5.88 0.06 0.17 6
18 5.56 0.06 0.18 #N/A
19 5.26 0.05 0.19 #N/A
20 5.00 0.05 0.20 #N/A
21 4.76 0.05 0.21 #N/A
22 4.55 0.05 0.22 #N/A
23 4.35 0.04 0.23 #N/A
24 4.17 0.04 0.24 #N/A
25 4.00 0.04 0.25 #N/A
26 3.85 0.04 0.26 #N/A
27 3.70 0.04 0.27 #N/A
28 3.57 0.04 0.28 #N/A
29 3.45 0.03 0.29 #N/A
30 3.33 0.03 0.30 #N/A


"Gary''s Student" wrote:

I think your MATCH is working perfectly!!

Here is what I get for the first 10 rows:

1 100 1.0000000 0.01 1
2 50 0.5000000 0.01 #N/A
3 33.33333333 0.3300000 0.01 #N/A
4 25 0.2500000 0.01 #N/A
5 20 0.2000000 0.01 #N/A
6 16.66666667 0.1700000 0.01 #N/A
7 14.28571429 0.1400000 0.01 #N/A
8 12.5 0.1300000 0.01 #N/A
9 11.11111111 0.1100000 0.01 #N/A
10 10 0.1000000 0.01 #N/A

All of column C except C1 is correctly #N/A because, with the exception of
the first row, all the values in column A are greater than 1 and all the
values in column C are less than 1. If match_type is 0, MATCH finds the
first value that is exactly equal to lookup_value. Lookup_array can be in any
order.




Column D is blank and column E has no effect on column F

--
Gary''s Student


"PaulW" wrote:

In column A I have a list of numbers from 1 to 300.
In column B I have =100/A1 dragged down.
In column C I have = ROUND(B1,0)/100

In column E I have numbers starting at 0.01, and increasing by 0.01.
In column F I have =MATCH(A1,$C$1:$C$300,0)

Some of these are errors, I don't have the first clue why.
0.05 gives the answer 19, so on row 19 I can see that, 100/19 is 5.26316, I
can see that this is rounded to 5, then when divided by 100 is now 0.05, and
this if the first instance of 0.05 in column C. Thats perfect.
0.06 gives an error though. But I can see that on row 16, 100/16 = 6.25000,
which when rounded and divided by 100 is now 0.06. There are 3 rows where the
answer is 0.06 (16, 17, 18) but match can't seem to find any of them?
Similarly, I can see that there are errors for 0.10 to 0.16, where I can
clearly see in column C that the numbers 0.14, 0.13, 0.11 and 0.10 are
present.

I tried changing it to a vlookup (although I need it as a match) and the
errors were still aparantly. I've tried changing the match type in the
formula to -1, this give me an answer for each one, but quite often the wrong
answer...

I've done a formula of =E6=C16 to see if both 0.06's are the same, and it
returns as TRUE, so why can't MATCH find it?

I see why.


Instead of propagating column E with
=E1+.01 in E2 use
=ROUND(E1+0.01,2)

Now we get matches thru row 11
--
Gary's Student


"PaulW" wrote:

I knew I'd get something wrong, column F should be
=MATCH(E1,C$1:$C$300,0)

Also, column E goes up by 0.01 each time.
In Your example (only the first 10 rows) they should be all errors apart
from the last row, where it searches for 0.10 (E10) and find it in row 10 of
column C.
If you continue it down to row 20 you'll get an answer for 0.05, and if you
keep going to row 30 you'll get an answer for as little as 0.03.

You can see in the below example that theres an error for 0.06, but it can
be planely seen in column C. Even row 10 is an error...

1 100.00 1.00 0.01 #N/A
2 50.00 0.50 0.02 #N/A
3 33.33 0.33 0.03 29
4 25.00 0.25 0.04 23
5 20.00 0.20 0.05 19
6 16.67 0.17 0.06 #N/A
7 14.29 0.14 0.07 14
8 12.50 0.13 0.08 12
9 11.11 0.11 0.09 11
10 10.00 0.10 0.10 #N/A
11 9.09 0.09 0.11 #N/A
12 8.33 0.08 0.12 #N/A
13 7.69 0.08 0.13 #N/A
14 7.14 0.07 0.14 #N/A
15 6.67 0.07 0.15 #N/A
16 6.25 0.06 0.16 #N/A
17 5.88 0.06 0.17 6
18 5.56 0.06 0.18 #N/A
19 5.26 0.05 0.19 #N/A
20 5.00 0.05 0.20 #N/A
21 4.76 0.05 0.21 #N/A
22 4.55 0.05 0.22 #N/A
23 4.35 0.04 0.23 #N/A
24 4.17 0.04 0.24 #N/A
25 4.00 0.04 0.25 #N/A
26 3.85 0.04 0.26 #N/A
27 3.70 0.04 0.27 #N/A
28 3.57 0.04 0.28 #N/A
29 3.45 0.03 0.29 #N/A
30 3.33 0.03 0.30 #N/A


"Gary''s Student" wrote:

I think your MATCH is working perfectly!!

Here is what I get for the first 10 rows:

1 100 1.0000000 0.01 1
2 50 0.5000000 0.01 #N/A
3 33.33333333 0.3300000 0.01 #N/A
4 25 0.2500000 0.01 #N/A
5 20 0.2000000 0.01 #N/A
6 16.66666667 0.1700000 0.01 #N/A
7 14.28571429 0.1400000 0.01 #N/A
8 12.5 0.1300000 0.01 #N/A
9 11.11111111 0.1100000 0.01 #N/A
10 10 0.1000000 0.01 #N/A

All of column C except C1 is correctly #N/A because, with the exception of
the first row, all the values in column A are greater than 1 and all the
values in column C are less than 1. If match_type is 0, MATCH finds the
first value that is exactly equal to lookup_value. Lookup_array can be in any
order.




Column D is blank and column E has no effect on column F

--
Gary''s Student


"PaulW" wrote:

In column A I have a list of numbers from 1 to 300.
In column B I have =100/A1 dragged down.
In column C I have = ROUND(B1,0)/100

In column E I have numbers starting at 0.01, and increasing by 0.01.
In column F I have =MATCH(A1,$C$1:$C$300,0)

Some of these are errors, I don't have the first clue why.
0.05 gives the answer 19, so on row 19 I can see that, 100/19 is 5.26316, I
can see that this is rounded to 5, then when divided by 100 is now 0.05, and
this if the first instance of 0.05 in column C. Thats perfect.
0.06 gives an error though. But I can see that on row 16, 100/16 = 6.25000,
which when rounded and divided by 100 is now 0.06. There are 3 rows where the
answer is 0.06 (16, 17, 18) but match can't seem to find any of them?
Similarly, I can see that there are errors for 0.10 to 0.16, where I can
clearly see in column C that the numbers 0.14, 0.13, 0.11 and 0.10 are
present.

I tried changing it to a vlookup (although I need it as a match) and the
errors were still aparantly. I've tried changing the match type in the
formula to -1, this give me an answer for each one, but quite often the wrong
answer...

I've done a formula of =E6=C16 to see if both 0.06's are the same, and it
returns as TRUE, so why can't MATCH find it?

Cheers both of you.

I am making E2 to be =E1+$E$1 and dragging down, at least I was.

How can they mathmatically not be correct though?
Especially when a =E6=C16 produces TRUE()?

I've used both your solutions, and its fixed, but I still can't get my head
round the logical reason *why* it didn't work initially....

"bj" wrote:

if you used the drag down method on column E to generate ther numbers, they
may not be exact. try goint to column F and entering
=round(E1,2)
and drag down.
use columm F as you match and try it.

The errors arise due to rounding in the translation of the numbers from
binary to decimal. It's probably off in the 15th digit. Some of Excel's
functions are smarter than others, and correct for this rounding error, but
apparently MATCH isn't so smart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"PaulW" wrote in message
...
Cheers both of you.

I am making E2 to be =E1+$E$1 and dragging down, at least I was.

How can they mathmatically not be correct though?
Especially when a =E6=C16 produces TRUE()?

I've used both your solutions, and its fixed, but I still can't get my
head
round the logical reason *why* it didn't work initially....

"bj" wrote:

if you used the drag down method on column E to generate ther numbers,
they
may not be exact. try goint to column F and entering
=round(E1,2)
and drag down.
use columm F as you match and try it.