Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: any cricket fans.....

  1. #11
    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: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

    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 ]

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

    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. #13
    New Member
    Join Date
    Jan 2010
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: any cricket fans.....

    Hint:

    Use IF and Round functions

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
  •