Array formula to select best data from a row ignore blank cells

[Pewil-2324]

I am trying to create a ranking table:
1. The attached excel sheet shows example of data
2. Conditional formatting used to highlight top 3 results
3. Best 3 array formula used to total best 3 scores
4. I would like to rank the final results highest to lowest.
5. Actual data being approx. 40 people and 50+ results
Objective
1. highlight best 18 results from a possible 55
2. This will be on an ongoing basis
3. Players each round will be ranked.
4. separately I would show the best scores for each person.
I have tried 2 formulas:
=LARGE(B3:K3,1)+LARGE(B3:K3,2)+LARGE(B3:K3,3) (This ignores blanks as wanted)
=SUM(LARGE(B6:AY6,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18})) This does not ignore blanks and returns #NUM!
Problem:
• Formula used will not work if "the required best of figure" is more than the actual number of recorded figures -eg: Aiden- Cell shows #NUM! The best of figure is 6 would
Blanks should be ignored

[AliGW]

Welcome to the forum.

Try this:

=SUM(LARGE(IF((B3:K3<>""),B3:K3),{1,2,3}))

and this:

=RANK(M3,$M$3:$M$7,0)

[CARACALLA]

M3=IF($A3<>"",SUMIF(B3:K3,">="&LARGE(B3:K3,3)),"")

Copy down

[Pewil-2324]

Thank you very much Ali - all the hours spent trying to find an answer!!
This worked both on my sample sheet and my main sheet, the only issue being I had #NUM error on my main sheet- there was only 1 data in the row. Checked it on my sample sheet and the same thing happed, is there away around this?

tnk

Peter

[AliGW]

Yep!

=IF(COUNT(B3:K3)<3,SUM(B3:K3),SUM(LARGE(IF((B3:K3<>""),B3:K3),{1,2,3})))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Pewil-2324]

Thank you Ali for the ranking formula, just had a play wit it so as to understand it better so I can apply it over a wider range- thank you.
If you are able to advise on the #NUM! issue mentioned ealier if you are able🤞

thanks for your help and support,

C

Peter

[Pewil-2324]

certainly will- new to this! but really found it helpful- thanks again.

[Pewil-2324]

Sorry Ali,
the formula covers where there is only one data cell, however f I have data in say 9 cells and I can only as to select the best of those 9- if I ask it to select the best 10- then once again the #NuM! sign appear.
could you modify the formula to include where I am asking it to select the best of (say 10 in my example) although data is only in 7 cells?

tnx

Peter

[AliGW]

Just change the numbers:

=IF(COUNT(B3:K3)<10,SUM(B3:K3),SUM(LARGE(IF((B3:K3<>""),B3:K3),{1,2,3,4,5,6,7,8,9,10})))

[Pewil-2324]

Thank you once again

Peter

[AliGW]

You’re welcome.