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.

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.

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.

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

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.

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.

=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.

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.....

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

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

Originally Posted by daddylonglegs

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?

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.....

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.

bringing it up.