+ Reply to Thread
Results 1 to 17 of 17

Points system

  1. #1
    Registered User
    Join Date
    06-27-2007
    Posts
    19

    Points system

    Hi all,

    Currently I'm working on a prediction game using excel. Can excel do the tabulation itself(Points awarded) if I just key in the result? Can anyone help me? Thanks in advance.
    Examples below,

    Result 3-1

    Name Predicted Scored Points
    Alex 3-1 5
    Brian 4-1 4
    Charlie 2-0 3
    Derrick 3-3 1
    Eric 0-2 0

    Point System:
    1 point for correct goals/no goal by either both teams
    3 points for correct result ( Win, Lose, or Draw - say result is 3-1, 3 points if you predicted a Chelsea 2-0 win)
    5 points for correct match results
    No points for incorrect score and results.

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Does this help?
    Attached Files Attached Files
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Registered User
    Join Date
    06-27-2007
    Posts
    19
    Hi oldchippy,

    Thanks for the help rendered. Really appreciated. It my fault that I didn't mention the point system in details. Perhaps you can visit my webpage at http://web.singnet.com.sg/~chelcfan/table.html
    and you will be clearer about my point system.
    Is excel possible to tabulate the points to each gamer in the 1st table automatically by just key in the result and tabulate again when key in another different result. Hope you can help me. I'm amateur in excel. Thanks again.

  4. #4
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Can you check this out and see if this is OK?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-27-2007
    Posts
    19
    Quote Originally Posted by oldchippy
    Can you check this out and see if this is OK?

    Hi oldchippy,

    Thanks, you got it right unfortunately there are error in the point system. I've attached 3 sheet on 3 scenerio and type the error by the side. Hope you can help me again. Thanks.
    Attached Files Attached Files

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    You can do without the helper columns if you wish and just use a single formula.

    Assuming your setup with actual score in B2 and D2 then with predicted scores in B4 and D4 use this formula in E4 (or even M4)

    =IF(COUNT(B$2,D$2,B4,D4)=4,IF(SIGN(B$2-D$2)=SIGN(B4-D4),3,0)+(B4=B$2)+(D4=D$2),"")

    and copy down the column

    This will give you the correct points score but also gives a blank if predicted score/actual score is blank

  7. #7
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Hi daddylonglegs,

    You seem to have cracked it here, thanks for your help

  8. #8
    Registered User
    Join Date
    06-27-2007
    Posts
    19
    Hi oldchippy and daddylonglegs,

    Thank you very much to both of you for the help. It is working. I've have beening cracking my head on this for weeks. If both of you happens to drop by Singapore, drop me an email and I'll bring you to taste the best local food here.

  9. #9
    Registered User
    Join Date
    06-27-2007
    Posts
    19
    I've workout the Table formula but the way I'm doing is very tedious bcos I need to input 60 over matches. Examples look at column E to I Is there a shorter formula from sheet 1 to rest of the sheets. Pardon for my ignorance in Excel.
    If in the middle of the game, if there is a Gamer name 'Ace' and I want to put his name top in alphabetical order will it affect the calculations to the table?
    Thanks a million.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    06-27-2007
    Posts
    19

    Re: Points system....

    =COUNTIF.3D(Sheet1:Sheet41!M29,5)


    Help...I've just upgrade from office 2000 to office 2008. The above formula seems fine before I upgraded to office 2008. Office 2008 can read the above formula. Can any one help me? Thanks.

    I tried to upload the file to there is an error.

  11. #11
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Points system....

    COUNTIF.3D is not a native Excel function, it's from Morefunc add-in. You'll probably need to re-download Morefunc to use in your new Excel version (although I'm not sure whether Morefunc is compatible with 2008 version).

    Try googling morefunc for download site.....

  12. #12
    Registered User
    Join Date
    06-27-2007
    Posts
    19

    Re: Points system....

    I've download the morefunc into my excel 2007. Still unable to get the formula right on the 'Table' tab. I've attached the spreedsheet, can anyone help? Thanks
    Attached Files Attached Files

  13. #13
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Points system....

    Those formulas work for me, are you sure Morefunc is installed?

  14. #14
    Registered User
    Join Date
    06-27-2007
    Posts
    19

    Re: Points system....

    Quote Originally Posted by daddylonglegs View Post
    Those formulas work for me, are you sure Morefunc is installed?
    You are able to view the 'Table' tab in excel 2007? I've the 'Morefunc' in my excel, am I missing any steps to install the 'morefunc' into excel 2007 properly?

  15. #15
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Points system....

    I'm at work, I can't install Morefunc here but I have a workbook with Morefuinc "embedded". If I open that workbook at the same time as yours the formulas work, which indicates that the formulas themselves are OK.

    I believe you will see Morefunc in the add-ins list but I can't quite remember where that is in Excel 2007, I don't have that here either.....

  16. #16
    Registered User
    Join Date
    06-27-2007
    Posts
    19

    Re: Points system....

    Googled and it seems countif.3d just don't work on excel 2007 after installing the morefunc.
    Is there another formula to replace the countif.3d as below counting sheet1 to sheet 41

    =COUNTIF.3D(Sheet1:Sheet41!M29,5)

    M29 is fixed in every sheet, to count if it appears 5.

    Thanks in advance.

  17. #17
    Registered User
    Join Date
    06-27-2007
    Posts
    19

    Re: Points system....

    bringing it up.

+ Reply to 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