Help needed: Formula for World Cup Prediction Game

[Ritchaldo]

Hi guys, I am new to this, and fairly average at Excel.

I have created a game for World Cup predictions where you get points i.e.:

Guess the correct result only: 2 points
Guess the correct amount of goals each team scores: 1 point per team
Guess the correct score exactly: 5 points
Wrong result and score: 0 points

So if I had the actual correct score in cells A1 & B1, and my predictions started in cells A2 & B2 etc, how would I calculate that?

Thanks in advance.

[FDibbins]

Hi, welcome to the forum

knee-jerk would be some nested IF's, but more than likely some form of INDEX/MATCH. But to give better advice, I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[shg]

daddylonglegs posted a workbook several years ago that does this: https://www.excelforum.com/the-water...etition-5.html

[Ritchaldo]

Please see attached the upload.

[Ritchaldo]

Many thanks in advance. I have added a rules tab to explain exactly what I am looking for. In the other tabs you will see how my points work and conditional formatting that I have been using to manually calculate points. Not ideal.

[FDibbins]

OK so what do you need us to help with?
(maybe I missed it?)

[Ritchaldo]

Instead of me manually working out how many points each player gets for their prediction, I would like a formula that automatically inserts their points into the P column after each fixture based on me putting the actual score of the game in. I think maybe a few nested IF statements. I have tried & failed miserably.

[Ritchaldo]

I would like a formula to autonatically calculate a playerrs points in the P column after each match based on the correct score of the match v versus their prediction. I think maybe nested if's are required. I tried & failed miserably.