+ Reply to Thread
Results 1 to 9 of 9

Excel 2007 : Formula for Net Run Rate for Cricket

  1. #1
    Registered User
    Join Date
    02-24-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    5

    Formula for Net Run Rate for Cricket

    Hey...
    I've been working on a new table for the Cricket World Cup.. I have almost completed with it but the problem lies in the net run rate column (Column S). I have no idea what formula to use in Net run rate as there are many criteria to be looked upon.

    General Formula of Net Run Rate (Say for Team A) = (Runs Scored by Team A / Overs conceded by Team A)-(Runs Scored by Team B /Overs conceded by Team B)


    SCENARIOS

    1. Side that bats first wins

    * Team A bat first and set a target of 287-6 off their full quota of fifty overs. Team B fail in their run chase, early losses causing them to struggle to 243-8 in their 50 overs.
    * Team A's runrate is \frac{287}{50} = 5.74
    * Team B's runrate is \frac{243}{50} = 4.86
    * Team A's NRR for this game is 5.74 − 4.86 = 0.88 Assuming this was the first game of the season, their NRR for the league table would be +0.88.
    * Team B's NRR for this game is 4.86 − 5.74 = −0.88. If this was the first game of the season, their NRR for the league table would be −0.88.

    2. Side that bats second wins

    * Team A bat first and set a target of 265-8 off their full quota of fifty overs. Team B successfully chase, getting their winning runs with a four with sixteen balls (2.4 of the 50 overs) remaining, leaving them on 267-5.
    * Team A's runrate is \frac{265}{50} = 5.30
    * Team B faced 47.2 overs, so their runrate is \frac{267}{47.33} \approx 5.64
    * Assuming that Team A and Team B had previously played as in the game in scenario one, the new net run rate for team A would be \frac{287+265}{50+ 50}-\frac{243+267}{50+47.33} = \frac{552}{100}-\frac{510}{97.33} \approx 0.28

    3. Side that bats first is bowled out. Side batting second wins.

    * Team A bat first and are skittled out for 127 off 25.4 overs. Team B reach the target for the loss of four wickets off 25.5 overs, scoring a single to win the game and end with 128 runs.
    * Despite Team A's runrate for the balls they faced being 127 / 25.667 = 4.95 (2dp) because they were bowled out the entire 50 overs are added to their total overs faced tally for the tournament, and Team B are credited with having bowled 50 overs.
    * Team B actually scored at a slower pace, however they managed to protect their wickets. Thus, only the 25 .(5/6) overs are added to the seasonal tally.

    4. Side that bats second is bowled out. Side batting first wins.

    * Team A bat first and set a formidable 295-7 off their complement of 50 overs. Team B never get close, being bowled out for 184 off 35.4 overs.
    * As in scenario 2, 295 runs and 50 overs are added to Team A's tally.
    * However, Team B, despite facing only 35.4 overs, have faced 50 overs according to the NRR calculations, and Team A have bowled 50 overs.

    5. Both sides are bowled out, the team batting first therefore taking the points.

    * Team A bat first, and manage 117 off 24 overs on a difficult playing surface. Team B fall agonizingly short, reaching 112 off 23.3 overs.
    * In this case, both teams get 50 overs both faced and bowled in the overs column for the season, just as in example 1.

    6. The game ends in a tie

    * Runs and overs are added as in the examples above, with teams bowled out being credited with their full quota of overs. Thus, the net run rate will always be the same.


    This is my problem kindly help me with this.... Waiting for your reply

    P.S. : Please find attachment along with mail

    With Regards
    Nibin Varghese Charley
    Attached Files Attached Files
    Last edited by nibinenator; 02-24-2011 at 05:11 AM.

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

    Re: Formula for Net Run Rate for Cricket

    Using your sample - assuming XL2007+

    Please Login or Register  to view this content.
    Note the Array entry requirement.

  3. #3
    Registered User
    Join Date
    02-24-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Formula for Net Run Rate for Cricket

    It kind off worked but the criteria's 3 and 4 is not been fulfilled... when a team is all out within 50 overs then the overs conceded should be 50 overs and if a team batting second wins it before the alloted 50 overs then they should calculate only the overs they took..

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

    Re: Formula for Net Run Rate for Cricket

    The formula works exactly as requested - the 50 overs are only allocated to the NRR calculation where either 10 wickets fall (as appropriate) or 50 overs utilised.

    Given your sample data the NRR values would be (formatting aside)

    Please Login or Register  to view this content.
    If the above aren't correct then I would suggest you post the expected results.

  5. #5
    Registered User
    Join Date
    02-24-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Formula for Net Run Rate for Cricket

    Infact I kind of formatted the formula a little bit and it worked...

    =IF(SUMIF($D$6:$H$54,$N7,$G$6:$K$54)=0,"n/a",(SUMIF($D$6:$H$54,$N7,$E$6:$I$54))/(SUM(IF($D$6:$D$54=$N7,IF($F$6:$F$54=10,50,$G$6:$G$54)))+SUM(IF($H$6:$H$54=$N7,IF($J$6:$J$54=10,50,$K$6:$K$54))))-SUM(IF($D$6:$D$54=$N7,$I$6:$I$54,IF($H$6:$H$54=$N7,$E$6:$E$54)))/SUM(IF($D$6:$D$54=$N7,IF($J$6:$J$54=10,50,$K$6:$K$54)),IF($H$6:$H$54=$N7,IF($F$6:$F$54=10,50,$G$6:$G$54))))

    Thanx a ton... To come with this huge formula in a short period of time u guys r truly genius or I would say born with excel... Cheers

  6. #6
    Registered User
    Join Date
    01-29-2013
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Excel 2007 : Formula for Net Run Rate for Cricket

    Hello nibinenator,

    Can you please post the formula that you have reworked.

    Thank you

  7. #7
    Registered User
    Join Date
    03-24-2015
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    2010
    Posts
    1

    Re: Excel 2007 : Formula for Net Run Rate for Cricket

    Hi There..

    I am new to the world of excel and have a very minimal info .. Can anyone make a NRR calculator for me as I have a upcoming tournament. Details are given below :

    Tournament consist of 8 teams. 2 groups with 4 teams each. each team will play 3 matches within group. the basic need for this calculator is to calculate the NRR for each match..

    just make a simple calculator where I can just put some info like total runs total overs and it can calculate the NRR

    Thanks .. for any queries please send me an email on [email protected]

  8. #8
    Registered User
    Join Date
    04-24-2017
    Location
    COLUMBUS, OHIO
    MS-Off Ver
    7
    Posts
    1

    Re: Formula for Net Run Rate for Cricket

    Can some one please share the final Net run rate excel sheet?

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula for Net Run Rate for Cricket

    Quote Originally Posted by madhuinfo View Post
    Can some one please share the final Net run rate excel sheet?
    Welcome to the forum

    Looks to me like the original file is in post #1, and you would then need to apply the formulas as suggested. Beyond that, you will need to start your own thread.

    If you feel this thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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