cricket batsman highest score from list

[cricket_stoner]

this formula is to select a batsman's highest score from a list of scores, but with three ranking factors.

i have 3 rows of data. C is a score, D indicates not out with a * if needed, and E is the amount of balls faced. so 15* (18) is 15 not out off 18 balls.

i already got help to work out the highest score and join it with the * if needed, but what if i wanted to add the balls faced to the displayed result. at the moment it says 15* but can i make it say 15* (18)?

CONCATENATE(LARGE(C5:C24,1),IF(LARGE(C5:C24,1)="","",INDEX($D$5:$D$24,MATCH(LARGE(C5:C24,1),$C$5:$C$24,0))))
this works fine without balls faced

basically, it would select the highest number in C, and if there's more than one of the same value, it will choose the not out one (with a * in D), and if both or neither are not out, it will choose the one with the lowest balls faced (E). obviously

so 15* (20) is better than 15 (10) (not out is better)
but 15* (10) is better than 15*(20) (both not out, 10 balls is quicker)

dunno how clear that is... cheers guys

[cricket_stoner]

i tried this

=CONCATENATE(LARGE(C5:C24,1),IF(LARGE(C5:C24,1)="","",INDEX($D$5:$D$24,MATCH(LARGE(C5:C24,1),$C$5:$C$24,0))))&" ("&MIN(IF(C5:C24=MAX(C5:C24),IF(E5:E24,E5:E24)) )&")"

works ok only problem is when you have a 78 and a 78*, it will select the balls faced for the 78 if it's lower. 78* is better irrespective of balls faced, it only comes down to balls if the score and out/notout is the same.

also it seems that if there's two scores the same, it only checks and displays not out if it's the higher up one of the two in the row.

tried to copy and paste other people's wisdom together to fit my problem... didn't work. worth a try but now it's over my head... where's mr longlegs?

[daddylonglegs]

I'm assuming that D5:D24 would only have an asterisk or blank. You could try this formula which needs to be "array-entered"

=MAX(C5:C24)&IF(SUM((C5:C24=MAX(C5:C24))*(D5:D24<>"")),"*","")&"("&MIN(IF(C5:C24&D5:D24=MAX(C5:C24)&IF(SUM((C5:C24=MAX(C5:C24))*(D5:D24<>"")),"*",""),E5:E24))&")"

Note: to "array-enter" a formula, select cell, press F2 then hold down CTRL+SHIFT keys while pressing ENTER. Curly braces like { and } will appear around the formula in the formula bar

[cricket_stoner]

your assumption was correct and your formula was perfect. i'm surprised you even understood what i meant let alone solve the whole thing in a few lines. brilliant, thanks a lot.