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.
精彩评论