Ive made a stats sheet for my cricket team and im having a few issues with some of the figures i want to show...
On the first sheet named "Season 2016" i have the top 5 stats in different categories using the Index formula based on the stats of the two pages, "Batting stats" and "Bowling Stats". The two problems i am having are.


1. If two people have the same figures e.g two players have taken the same amount of wickets, then it shows the same name twice instead of the two names that have the same number of wickets.
2. I cant find a way to return the best bowling figures as i cant get the Index function to return this. Again these numbers are on the "Bowling Stats" page under the Best Figures Column and are ranked on......highest number - lowest number

Attached Files

2016.xlsx (176.7 KB, 19 views)

Hello & Welcome to the Forum,

Well up front I'll say I know nothing about cricket, but maybe I can help with some formulas.

Q1: How about on the Bowling Stats tab, use a unique rank in column L.
=RANK(K4,$K$4:$K$18,0)+COUNTIF($K$4:K4,K4)-1

Q2: I see you have 6-5 as the best figure followed by 4-13. Is it as simple as the 6-5 is 6 wins and five loses. If that is so, maybe do another unique count on the left part of 6-5?

no, neither of those work

What do you mean neither of these worked? A greater and more detailed explanation of what you tried is always welcomed.

Look at the areas in grey

Attached Files

2016 (1).xlsx (177.7 KB, 15 views)

hi, after looking at the files you have attached i can see that it works but not quite right. the "Best Figures" table should be sorted by highest number - lowest number. for example the bottom two results on the best figures table on the season 2016 sheet should be the other way around

thank you for your help

If I understand correctly, then in E17 copied down

=INDEX('Bowling Stats'!$F$4:$F$18,MATCH(ROW($A$5)-(ROW(A1)-1),'Bowling Stats'!$N$4:$N$18,0))

no, sorry it wasnt very clear. i meant it should be sorted by the highest number first then the lowest number second
for example, 4-6 would be better than 4-10
so in the sheet the bottom 2 numbers would be the other way round as 2-7 is better than 2-15

Here is a method which rates by winning percentage?

Attached Files

2016 (1) (3).xlsx (177.3 KB, 24 views)

No as its not a stat to do with results. The first number is the amount of wickets taken by the bowler and the second number is the number of runs given away in taking those wickets, so the best figures would be the most wickets for the least amount of runs. with the stats that are in there at the moment the order would be...
6-5
4-13
3-6
2-7
2-15
but these could change as the season progresses.

With the setup I have in the last attached workbook, can you sort column F (Largest to Smallest) and then column G (Smallest to Largest)?

If you do that then everything will run 1, 2, 3, 4, 5 etc down the line.

H4 copied down -- >> =RANK(F4,$F$4:$F$18,0)+COUNTIF($F$4:F4,F4)-1

And actually you wouldn't even need column H as again, every thing is in order.

E17 copied down -- >> ='Bowling Stats'!$F4&"-"&'Bowling Stats'!$G4
D17 copied down -- >> ='Bowling Stats'!$A4

would it be possible for you to upload the worksheet with that formula in please?

Like this...

Attached Files

2016 (1) (3).xlsx (177.3 KB, 18 views)

problem with that is when you change the order of the table on the bowling stats page then it change the table for best figures on the first page

as i put the stats into this document it updates individual players stats on their own workbook. would it be eaiser to somehow take the best 5 figures from each individual players pages and put them into a hidden page into this workbook and then sort them out on there?

That could be a method, but you see the challenge here for us. We do not know the full operation of your spreadsheet and what is possible and what's not possible.

Have a play with the sheet and I'll be back later. We'll be out of touch for the next 10 hours or so.