any cricket fans.....

[lucky]

I have the task of compiling a seasons worth of bowling and batting figures for the local cricket team. Something I wish to achieve, is a simple way of representing 6 balls as 1 over and 21.3 to mean 21 overs, 3 balls. So when I total some figures, like e.g.
0.3
3.4
9.5
9.2

to equal 23 overs and 2 balls instead of 22.4 (if calculated)
If anyone has tackled this problem before please let me know.
Cheers!

 

[Yogi Anand]

On 2002-04-30 14:35, Chris Davison wrote:
Nice try Yogi, I'm impressed ! but seeing as it's cricket, this may need an Englishman's touch

:wink:

Lucky : try this somewhat convoluted formula, at the very least, it'll flag your post back up to the top and maybe someone can simplify it a bit :

=(SUM(TRUNC(TEXT(A1:A10,"###0.0")))+TRUNC(SUM(RIGHT(TEXT(A1:A10,"###0.0")*1)/6))&"."&MOD(SUM(RIGHT(TEXT(A1:A10,"###0.0"))*1),6))*1

it allows for blank cells.

Please note : this is an array formula : ths means that when you type it in, you will get a #VALUE error... don't panic, it means it has to be array-entered, so hit "F2" so the formula shows up in the formula bar then hit CONTRL and SHIFT and ENTER all at the same time

your formula should now appear with curley brackets around it (indicating an array formula) and should be the result of the overs and balls which populate cells A1 to A10

hopefully : 23.2 as per your original example

Howzat ?

Hi Chris:
Are you guys still playing cricket? Oh, pardon me ... cheap joke. Any way good job at adding up overs and balls. By the way my formula works -- look at my recent post wherein I laid down some of the prerequisites to entering the data.
I have not analyzed you formula, but with Lucky's second set of numbers that prompted you to develop your formula, your formula does not appear to give right results ...

2.2
1
5.3
11

your formula results in 20.5 instead of 19.5

Besides, as I mentioned in my original response, I don't like the idea of faking up overs and balls with the decimal point as a separator. The decimal point is universally recognized (in most of the countries, anyway) as a separator for numbers based on the decimal system. So, in my opinion, it is not a good idea to mix up base 10 and base 6 numbering system in one.

I am sure the problem with your formula is a minor one, but as I said I have not looked at the makeup but just the result from your formula.

Regards!
Yogi Anand

N.B.
If it does not bother you to represent the overs and balls with a decimal separator, here is a formulation that will work nicely ...

=SUM(INT(A1:A4))+INT(SUM(MOD(A1:A4,1))/6)&" overs "&MOD(SUM(MOD(A1:A4,1)),6)*10&" balls"
and with ...

2.2
1
5.3
11

it will give 19 overs and 5 balls. I hate to call the sum of the above numbers as 19.5
This message was edited by Yogi Anand on 2002-04-30 15:51

 

[Chris Davison]

Yogi,

"are we still playing cricket ?"

oh yes.... if it wasn't for the bloody Aussies, we'd be World Champions !

Thanks for hacking my formula, it's somewhat quaint how the system has still remained from the 1700s of decimal overs and 6 balls in an over....

The scores are similar in confusion too : 225 runs with the fall of 4 wickets is always notated as 225/4 !

with these we regularly get run-rate stats... ie total score / overs&balls

:wink:

Nice work on your formula, as always !

Chris

 

[Kabeerahmed]

Hint:

Use IF and Round functions