I would like to rank stocks based on a composite value. The composite value would use 2 variables. For example, the highest rate of change over the last 6 months and the highest score for an indicator called "X". I don't simple sorting can accomplish this. How can I link the stocks with the different values? Looking for suggestions.
Create a helper column to calculate this composite score, then sort based on the composite score. That would be my best guess. If you give us an example worksheet, perhaps we can be of more assistance!
If I helped you, please give me reputation.
Are the two scores of the same weight or is one the primary rank factor and the other secondary?
Are the two scores of the same magnitude (i.e. 1-100)?
As Jk says, you will probably need a dummy column (which you can hide if you like)
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
The 2 values have the same weight. I'm at work now but can post an image later this evening
Okay, let's say score 1 is in col A, score 2 in col B,
Assuming same magnitude for the two scores. Insert a col C with formula =IF(ISNUMBER(A2),A2+B2,"") copied down (again, you can hide this if you want)
Then in D2
=RANK(C2,$C$2:$C$500)
Does that work for you?
If the magnitudes are different, i.e. Score 1 can range from 1 to 500 and score 2 ranges from 1 to 100, then you need to normalize
in C2 = IF(ISNUMBER(A2), A2/5 + B2, "")
=RANK(C2,$C$2:$C$500) just adds col A and col B. 1st I want to rank col A - give it a value from highest to lowest then rank col B - give it a value from highest to lowest, then add the 2 ranks from col A and col B
Then try putting this in C2:
Then in D2:, put
Please Login or Register to view this content.Please Login or Register to view this content.
=(RANK(A2,A:A)+RANK(B2,B:B)) is what I'm looking for but I had to put it in as
(@RANK(F5,F5..F21,0)+@RANK(H5,H5..H21,0)) and it wouldn't copy down correctly...kept increasing the range to h22,h23 and so on, so had to edit every cell. Now I need to do a final sort, but when I do to values are coming up different
(@RANK(F5,F5..F21,0)+@RANK(H5,H5..H21,0))
In order to keep cells from increasing (that you don't want to increase, the $ acts as an anchor
try
(@RANK(F5,$F$5:$F$21,0)+@RANK(H5,$H$5$H$21,0))
Great, now is possible to link the name of the stock in col A with the final composite value?
Depending on the number of data points you have, you don't want to stop the range at F21. You could try either (RANK(F5,F:F) or (RANK(F5:$F$5:$F$9999) to make sure you stay within a growing list.
What do you mean by "linking" the name? You could create another helper column and use:That assumes your name is in A5, and the composite rank is in I5. It'll generate [Name]-[Composite Rank] in that other help column.Please Login or Register to view this content.
Never mind, I forgot to include a "$" in one of the formulas. Did a simple sort and everything works. I looked at the RANK function before but the percentile thing made me think it didn't apply. Thank again for the help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks