# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Excel 2007 : Formula for Net Run Rate for Cricket

## nibinenator

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

----------


## DonkeyOte

Using your sample - assuming XL2007+




```
Please Login or Register  to view this content.
```


Note the Array entry requirement.

----------


## nibinenator

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..

----------


## DonkeyOte

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.

----------


## nibinenator

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

----------


## nbharath

Hello nibinenator,

Can you please post the formula that you have reworked.

Thank you

----------


## Faizanabbasi

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 faizan.abbasi@ae.ey.com

----------


## madhuinfo

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

----------


## FDibbins

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



Welcome to the forum  :Smilie: 

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.

----------

