Cricket Spreadsheet

Originally Posted by kingspur06

Hi Guys & Girls,
I am trying to set up a spreadsheet to enter cricket stats.
It's been fine until I come to 'number of overs bowled'
The problem is that there are only six balls in an over.

I need excel to recognise that for instance when i enter '5.5' it means '5 overs and 5 balls' not '5 and a half' overs.

I am assuming that i need to change the cell properties from calculating in decimal to calculating in groups of six.

At the moment as a workaround I am just entering 'balls bowled' instead of 'overs bowled'

Any help would be much appreciated

thanks
Mark

Hi,

if you enter as 5.5, then

=SUM(IF(A1:A5>0,INT(A1:A5)))+INT((SUM(A1:A5)-SUM(IF(A1:A5>0,INT(A1:A5))))*10/6)+((((SUM(A1:A5)-SUM(IF(A1:A5>0,INT(A1:A5))))*10/6)-INT((SUM(A1:A5)-SUM(IF(A1:A5>0,INT(A1:A5))))*10/6))*6/10)

will accumulate as Overs / Balls

I had also

=SUM(IF(A1:A5>0,INT(A1:A5)))+INT(SUM(IF(A1:A5>0,MOD(A1:A5,1)))*10/6)+(MOD(SUM(IF(A1:A5>0,MOD(A1:A5,1)*10/6)),1))*6/10

or

=SUM(INT(A1:A5))+INT(SUM(MOD(A1:A5,1))*10/6)+(MOD(SUM(MOD(A1:A5,1)*10/6),1))*6/10

all of which require Ctrl/Shift/Enter rather than Enter as formula.

hth---

Closing thread - duplicate posting
http://www.excelforum.com/showthread.php?t=597635