开发者

MATCH() only returns one of two values in Excel

开发者 https://www.devze.com 2023-02-08 19:46 出处:网络
I am using Excel to do propensity score matching (not the best tool for this, I know, but that\'s the assignment).

I am using Excel to do propensity score matching (not the best tool for this, I know, but that's the assignment).

I am using MATCH() to find the entry in colO (that's letter O) closet to the entry in colM. My call works properly in libreoffice, but not in Excel. All the values in colO and colM are probabilities (i.e., between zero and one inclusive).

=MATCH(M11, O$11:O$60, 1)

If the value in colM is zero, then MATCH() returns 28, which is also a zero in colO. If the value in colM is greater than zero, then MATCH开发者_运维技巧() returns 50, which is the smallest non-zero value in colO.

Any ideas? I don't typically use Excel. Oh, and here's the top of these columns:

propensity  health w/o vaccine  propensity  match
0                  2           0.393115219   28
0                  1           0.280598404   28
0.747918388        0           0             50
0.843491818        0           0             50
0.707782271        0           0             50
0.829627573        0           0             50
0.515298454        0           0             50
0.464636469        0           0             50
0.705333684        0           0             50
0                  3           0.641132271   28
0                  4           0.366445099   28
0.590387706        0           0             50
0.859441341        0           0             50
0                  2           0.304260844   28
0                  3           0.725971617   28


When you use 1 or -1 (not zero) as the third argument of MATCH, column O needs to be sorted. If you want to find the closest match on an unsorted list, you need to use an array formula

=MATCH(MIN(ABS(M11-$O$11:$O$25)),ABS(M11-$O$11:$O$25),FALSE)

Enter an array formula with Control+Shift+Enter, not just enter. Excel will put curly braces around the formula.

The first argument finds the minimum difference between M11 and every value in Column O. The second argument is a complete list of all the differences. The MATCH matches that minimum value to the complete list and returns the row on which it's found. I get

3
3
15
15
15
15
1
1
15
3
3
10
15
3
3

Which at first glance looks correct. Change the $O$25 in the formula to the end of your data in column O.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号