Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: any cricket fans.....

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!







  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Lucky

  6. #6
    New Member
    Join Date
    Feb 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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








  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?


    :: Pharma Z - Family drugstore ::

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  10. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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