Thanks:  0
Likes:  0

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

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

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 ]

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

Nice work on your formula, as always !

Chris

3. ## Re: any cricket fans.....

Hint:

Use IF and Round functions

## User Tag List

#### Posting Permissions

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