Forum Discussion
Looking to rank close data sets
I'm new to the community, and looking for a formula that might be able to help with some data I'm looking to sort. I'm analyzing mutual fund returns, and want to determine the "best" fund based on returns over many different time periods (1 mo, 3 mo, 1 yr, 3 yr, 5 yr, etc.)
Some returns are so close (within .05 of a percentage), it doesn't make sense to use a simple RANK formula. In the example below, is there a way to award Fund 1&2 3 points, Fund 3-5 2 points, and Fund 6 1 point?
| Fund 1 | 22.73 |
| Fund 2 | 22.70 |
| Fund 3 | 22.22 |
| Fund 4 | 22.19 |
| Fund 5 | 22.18 |
| Fund 6 | 20.99 |
Thank you for the time and expertise!
First off if you will create hard criteria like that I would recommend a lookup table like min Val, points and then you can use that table in the formula below instead of 'hard coding' the values into the formula. It will make tweaking those cut-off values much easier. But conceptually here is the idea.
If you want a 'helper' column to show/display the points you can use something like:
=Xlookup(B1:B6, {0, 22, 22.5}, {1, 2, 3}, 0, -1)
if you will stick with the 1,2,3 point then you could use =Xmatch(B1:B6, {0,22,22.5},-1)
you can also just use that inside a calculation like
=SUM( Xlookup(B1:B6, {0, 22, 22.5}, {1, 2, 3}, 0, -1) )
and again I would recommend using a table instead of these hard coded values so if the table is in G2:G4 for min values and H2:H4 for points then it would be:
=Xlookup(B1:B6, $G$2:$G$4, $H$2:$H$4, 0, -1)
or even better yet use the 'format as a Table' option on the home menu and call that table pts and then:
=XLOOKUP(B1:B6, Pts[Min Val], Pts[Points], 0,-1)
I will attach example file
4 Replies
- yes but what is the criteria? I know you look at them and it is "clear" to you but when programming even using excel worksheet functions you need to set out clear conditions and those conditions should be valid for all cases. For example if you say any 2 numbers < 0.1 apart must be assigned the same rank/pts that is a problem if you have a sequence of values like 22.00, 22.05, 22.10, 22.15, ... because 22.05 would need to be the same as 22.00 and 22.10 but the total span of numbers here is 0.15. So you need to first come up with what "rule" you want to be applied and then a formula can be derived.
- I see where I'm missing some criteria. My full data sets are much larger, but in the above example, I would want some like:
x>22.5 = 3 points
22.49>x>22.00 = 2 points
21.99>x = 1 point
Is that enough context to create a formula?First off if you will create hard criteria like that I would recommend a lookup table like min Val, points and then you can use that table in the formula below instead of 'hard coding' the values into the formula. It will make tweaking those cut-off values much easier. But conceptually here is the idea.
If you want a 'helper' column to show/display the points you can use something like:
=Xlookup(B1:B6, {0, 22, 22.5}, {1, 2, 3}, 0, -1)
if you will stick with the 1,2,3 point then you could use =Xmatch(B1:B6, {0,22,22.5},-1)
you can also just use that inside a calculation like
=SUM( Xlookup(B1:B6, {0, 22, 22.5}, {1, 2, 3}, 0, -1) )
and again I would recommend using a table instead of these hard coded values so if the table is in G2:G4 for min values and H2:H4 for points then it would be:
=Xlookup(B1:B6, $G$2:$G$4, $H$2:$H$4, 0, -1)
or even better yet use the 'format as a Table' option on the home menu and call that table pts and then:
=XLOOKUP(B1:B6, Pts[Min Val], Pts[Points], 0,-1)
I will attach example file
