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-25 09:33, lucky wrote:
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!

Hi Lucky, I suggest using a character like ~ instead of . as separator between overs and balls to alleviate confusion with real numbers. So your data will look like:

0~3
3~4
9~5
9~2

then use the following array formula:

{=SUM(LEFT(A2:A5,FIND("~",A2,1)-1)+0)+INT(SUM(RIGHT(A2:A5,1)+0)/6)&" overs "&MOD(SUM(RIGHT(A2:A5,1)+0),6)&" balls"}

to result in ... 23 overs and 2 balls

HTH

Please post back if it works for you .. otherwise explain a little further and let us take it from there!

 

[lucky]

Yogi
I copied your figures into A2:A5 and formula into A6 but that doesn't appear to work........#VALUE!

I like your idea though.

 

[Yogi Anand]

On 2002-04-26 01:44, lucky wrote:
Yogi
I copied your figures into A2:A5 and formula into A6 but that doesn't appear to work........#VALUE!

I like your idea though.

Hi Lucky:
Did you enter this formula as an array formula. An aarray formula is entered not just with the ENTER key but with CTRL+SHIFT+ENTER key

Please post back if it works for you now ... otherwise explain a little further and let us take it from there!

_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature
This message was edited by Yogi Anand on 2003-01-19 13:37

 

[lucky]

Yogi
Have never used Array Formulas before, but have sussed it. It works fine, thank you and may the luck be with you.

Lucky

 

[lucky]

Yogi
Actually......works very good for the example given, but when there are 2-digit figures (overs) or 0 balls, there seems to be a problem......Eg usign your formula

2~2
1
5~3
11

results 10 overs 0 balls (should be 19 overs 5 balls)

Also if a cell is empty in the range (e.g. no figure in cell A3) then the formula doesn't work.

Thanks for your time

 

[Chris Davison]

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 ?

 

[Jack in the UK]

this excel stuff beyond me its all leather on willow to me and a load of old stumps.

Im creasing up!
Bowled over even!
Im running it!
I cant wicket it!
For centrys ive tied!
im knocked for 6!

over to you Chris!

who stepped on a duck!

 

[Yogi Anand]

On 2002-04-30 06:32, lucky wrote:
Yogi
Actually......works very good for the example given, but when there are 2-digit figures (overs) or 0 balls, there seems to be a problem......Eg usign your formula

2~2
1
5~3
11

results 10 overs 0 balls (should be 19 overs 5 balls)

Also if a cell is empty in the range (e.g. no figure in cell A3) then the formula doesn't work.

Thanks for your time

Hi Lucky:
The formula was written to sum up 6 entries A2:A6 initially in your request. For your new data, I am going to use all double-digit overs and I am going to use six entries as in your original post, so using the formula you already have and using the data as described in this para ...

02~2
01~0
05~3
11~0
00~0

'{=SUM(LEFT($A$2:$A$6,FIND("~",$A$2,1)-1)+0)+INT(SUM(RIGHT($A$2:$A$6,1)+0)/6)&" overs "&MOD(SUM(RIGHT($A$2:$A$6,1)+0),6)&" balls"}

results in 19 overs and 5 balls

so the formula does work -- please note the requirements for entering the data.

If it is some thing that we want to apply without any restrictions, we can work on that to meet your requirements.

Hope This Helps!

 

[Yogi Anand]

On 2002-04-30 14:39, Jack in the UK wrote:
this excel stuff beyond me its all leather on willow to me and a load of old stumps.

Im creasing up!
Bowled over even!
Im running it!
I cant wicket it!
For centrys ive tied!
im knocked for 6!

over to you Chris!

who stepped on a duck!

Good try Jack -- but you got LBW'd on a left aligned, centered over selection spinner that merged to the right.

Regards!
Yogi Anand