+ Reply to Thread
Results 1 to 6 of 6

[Cricket] Selecting scores from list based on multiple factors.

  1. #1
    Registered User
    Join Date
    09-29-2007
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    55

    [Cricket] Selecting scores from list based on multiple factors.

    A couple more cricket questions - hopefully my last. These are similar to previous requests but different enough that I can't tweak them myself.

    HIGH SCORE
    I have a list of scores like this: 85*(75) or 85(75). The 85 is the score, the asterisk denotes a 'not out' score, and the 75 is the number of balls faced. I need a formula that selects the best score from the list with these criteria in mind:
    85(x) > 84(x) [Higher scores are better.]
    85*(x) > 85(x) ['Not out' scores are better than equal scores that aren't 'not out'.)
    85(75) > 85(76) [If both conditions above are equal, the score with the lowest amount of balls faced is better.]

    BEST BOWLING
    I have a list of figures like this: 3/35(8). The 3 is the number of wickets taken, the 35 is the number of runs conceded, and the 8 is the number of overs bowled. I need a formula that selects the best figures with these criteria in mind:
    3/35(x) > 2/15(x) [Higher number of wickets is better.]
    3/35(x) > 3/36(x) [If wickets are equal, lower conceded runs are better.]
    The value in brackets isn't really relevant, although it should still be displayed with the best figures. If I had to decide, I'd say that a higher value in the brackets should break a tie between best figures, but it's not required; displaying either would be fine.

    I've attached a file with test data, more information and expected results. Any help from the resident geniuses would be appreciated. Thanks guys.
    Attached Files Attached Files
    Last edited by cricket_stoner; 03-27-2010 at 01:46 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: [Cricket] Selecting scores from list based on multiple factors.

    For the first I confess given complexity I'd be inclined to split the calculation into two...

    First - create a number which represents the top score:

    C9:
    =MAX(--(LEFT(A1:A8,FIND("*",REPLACE(A1:A8,FIND("(",A1:A8),0,"*"))-1)*1000)+ISNUMBER(FIND("*",A1:A8))+(1000-SUBSTITUTE(REPLACE(A1:A8,1,FIND("(",A1:A8),""),")",""))/1000)
    confirmed with CTRL + SHIFT + ENTER
    (above assumes always less than 1000 deliveries are faced)

    Then create the final output based on the above value

    B9:
    =INT($C9/1000)&REPT("*",MOD($C9,10))&"("&ROUND((1-MOD($C9,1))*1000,0)&")"

    There's probably a more elegant method but the above sprang to mind first.

    I will take a look at the bb figures now if not answered in the interim.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: [Cricket] Selecting scores from list based on multiple factors.

    Regards the bb - similar to before - I'd be inclined to break into two component parts, ie first create a numerical reference:

    C29:
    =MAX(--(LEFT(A23:A28,FIND("/",A23:A28)-1)*1000)+1000-REPLACE(LEFT(A23:A28,FIND("(",A23:A28)-1),1,FIND("/",A23:A28),"")+(SUBSTITUTE(REPLACE(A23:A28,1,FIND("(",A23:A28),""),")","")/1000))
    confirmed with CTRL + SHIFT + ENTER

    and then re-create the string based on said number:

    A29:
    =INT(C29/1000)&"/"&1000-ROUND(MOD(C29,1000),0)&"("&ROUND(MOD(C29,1)*1000,1)&")"

    per the above assumptions are made regards number of runs likely to be conceded - namely less than 1000 ... even I'm not that bad!

    EDIT:
    the above is liable to error should no runs be conceded (as 1000-0 would equate to 1 wicket) - not sure if you need to account for this possibility or not... having looked further I think it would be ok.
    Last edited by DonkeyOte; 03-26-2010 at 07:37 PM. Reason: ref. to B29 should have been C29 and subsequent ref. to K29 should have been C29

  4. #4
    Registered User
    Join Date
    09-29-2007
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    55

    Re: [Cricket] Selecting scores from list based on multiple factors.

    Thanks DonkeyOte.

    They both work perfectly when the range is full of data, but when there are empty cells in the range, it gives a value error. There will usually be empty cells in the range too, as the figures are added gradually. (My fault for neglecting to mention that in the example data... and I thought I'd covered everything...)

    Any way to make it ignore blank cells?

    Thanks again.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: [Cricket] Selecting scores from list based on multiple factors.

    Simplest perhaps to add a pre-emptive ISNUMBER test to both arrays (C9/C29) ie:

    C9:
    =MAX(IF(ISNUMBER(--LEFT(A1:A8)),--(LEFT(A1:A8,FIND("*",REPLACE(A1:A8,FIND("(",A1:A8),0,"*"))-1)*1000)+ISNUMBER(FIND("*",A1:A8))+(1000-SUBSTITUTE(REPLACE(A1:A8,1,FIND("(",A1:A8),""),")",""))/1000))

    C29:
    =MAX(IF(ISNUMBER(--LEFT(A23:A28)),--(LEFT(A23:A28,FIND("/",A23:A28)-1)*1000)+1000-REPLACE(LEFT(A23:A28,FIND("(",A23:A28)-1),1,FIND("/",A23:A28),"")+(SUBSTITUTE(REPLACE(A23:A28,1,FIND("(",A23:A28),""),")","")/1000)))

    both of the above confirmed with CTRL + SHIFT + ENTER

  6. #6
    Registered User
    Join Date
    09-29-2007
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    55

    Re: [Cricket] Selecting scores from list based on multiple factors.

    You're a deadset genius. Thanks mate!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1