Forum Discussion

Copper Contributor

Mar 03, 2023

Solved

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 r...

excel

Formulas and Functions

mtarler
Mar 03, 2023
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
points.xlsx11 KB

mtarler

Silver Contributor

Mar 03, 2023

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.

DuncanPray

Copper Contributor

Mar 03, 2023

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?

mtarler
Silver Contributor
Mar 03, 2023
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
points.xlsx11 KB
- DuncanPray
  Copper Contributor
  Mar 06, 2023
  Thanks so much for your help! This was exactly what I was looking to do. Using a "Pts" table saved me a lot of time.