Closed Thread
Results 1 to 4 of 4

Cricket Spreadsheet-Is there anyway of formating a cell?

  1. #1
    Registered User
    Join Date
    04-24-2007
    Posts
    2

    Cricket Spreadsheet-Is there anyway of formating a cell?

    Hello,
    Please could you help me

    I am trying to set up a cricket spreadsheet which was all fine until I came to the 'overs bowled' section.
    The problem I have is that there are six balls per over.
    I need excel to recognise that, for instance, when I enter '5.5', I mean '5 overs and five balls' rather than 'five and a half' which is of course what it thinks I mean.

    Is there anyway of formating a cell so that it does not apply decimal rules, but allows me to calculate in sixths without using fractions.

    any help would be much appreciated
    many thanks
    Mark

  2. #2
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127
    I'm not familiar with cricket, but if you just need the value of the number to right of the decimal, you can use this formula (assuming the number is in cell A1):

    =VALUE(RIGHT(A1,LEN(A1)-SEARCH(".",A1)))

  3. #3
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    there are ways around it, but you would need to attach your sheet to get a better answer. Most would involve converting to balls. If overs were in A1

    Balls int(a1)*6,mod(a1,1)*10

    runs per over = runs per ball *6 etc

    if you have added all the balls together in say a10

    Overs=int(a1/6)+mod(a1,6)/10 at least to be displayed

    Regards

    Dav

  4. #4
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    Closing thread - duplicate posting

    http://www.excelforum.com/showthread.php?t=597633
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1