Ranking

[sfinns]

I have included a spreadsheet that attempts to rank share price relationship data choosing the highest ranking data too choos eteh appropriate stock, picking the stocks and then assessing the performance.. unfortunately the same rank appears for quite a bit of the data causing an error signal when i try and calculate the top ranking shares and their subsequent performance.

i want to be able to chose from 1 to 10 of the top performing ratios to figure out if I bought the shares what the performance would be for the week after.
if something ranks the same im indifferent which ranks first , i just want to have a clear rank that is not duplicated . also if the data registers at -100 it was not in existence and shouldnt be ranked at all.

many thanks

[rylo]

Hi

Try this in corrcalc!BE18. =RANK(C18,$C18:$BC18)+COUNTIF($B18:B18,C18)

This should give you a distinct ranking for the items.

Not sure what you can do about the -100 items, but as you are only chasing the top 10 or so, then these would not be considered, so they shouldn't be an issue.

HTH

rylo

[sfinns]

unfortunateley, this still shows duplication errors - any other ideas

[rylo]

give some examples of where there are duplicates.

rylo

[sfinns]

sorr , the problem occured further down in the data series as it is a very big model - i have extracted some of the oproblem dates - there are about 10 more but this shoudl give you a good subset - any other thoughts on an automated solution.

please see corrcalc a51 for data and rankings
and rank a51 for the sorting of 1 to 6 that generates errors due to duplication of data

many thanks

[rylo]

Hi

Just realised that you are not including column B in your data to be ranked. Is there a particular reason for this? What is happening is that the data is column B is sometimes in the C:BC range, and this is what is throwing things out. Or is this why you were trying to cover in your original formula?

rylo

[sfinns]

column b should not be ranked. i still dont think it works though can you help

[micope21]

Hi

I had a look and I can see the problem. I think can work. But need work on it. give me time.

Cheers

[rylo]

Hi

If column B shouldn't be included in the rankings, then can you insert a blank column as column C, then use the same cumulative approach? Is having a blank column in column C going to cause you serious problems?

rylo

[micope21]

Hi

I have spent good half hour.

I have to agreed with rylo. It up to you decide go with rylo suggest or work another way around it.

Cheers

[sfinns]

no it shouldnt ill check

[chu082011]

no it shouldnt ill check

Thanks for your information. It help me to think about my ideals.

We also find them more same at: Performance ranking

Tks again and pls keep posting.