I have created a document that allows me to input the cricket scores and then provides me with the players statistics. I am having issues with one area and that is displaying the bowlers best figures.
If you go to the "bowling statistics" tab and in cell K16:R16 that is the formula I am trying to solve. In cells K1:L1 are the names of the columns
password for all coding is "bradman"
So, what it does is, is turn the bowling figures like “5-15 (10)” into a number 51510, except this fails when the number of significant digits is different for each of the wickets (5), runs (15) and overs (10) from row to row.
{=IF($B16="",0,IF($AP16=0,0,INDEX($DB16:$XA16,0,MATCH(MAX(($DB$1:$XA$1={"Trial";"T20";"GPS";"AlanDavidson";"FiveHighs";"BarberisCup";"EastCoast"})*(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($DB16:$XA16,"-","")," ",""),"(",""),")","")))),VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($DB16:$XA16,"-","")," ",""),"(",""),")","")),0))))}
Instead we need to calculate a “bowling score” by assigning more value to wickets than runs than overs, eg
Wicket value: 10000
Run value: 10
Over value: 1
We can use the function
=VALUE(LEFT($DB19:$XA19,FIND("-",$DB19:$XA19)-1))*10000 * VALUE(TRIM(MID($DB19:$XA19, FIND("-", $DB19:$XA19)+1, 3)))*10 * VALUE(TRIM(SUBSTITUTE(MID($DB19:$XA19,FIND("(",$DB19:$XA19)+1, 10), ")", "")))
To calculate the “bowling score” for an individual cell in the format “5-15 (10)”, but it’s beyond my skillz to substitute that into the full array function.
Any help will be much appreciated.
Eg:
5-33 (10)
3-43 (4)
5-22 (11) - this one should be the best ranked figure.
5-22 (12.4)
first digit is the highest.
Second digits after the dash should be the lowest and
In brackets should be the lowest
Bookmarks