Question description:
This user has given permission to use the problem statement for this
blog.
For the world cup 5 of us have done score predictions for all of the group stage games. If the correct result is predicted ie a draw then 1 point is given and if the correct score is predicted then 3 points is given.
I have got the predictions in a table on excel and I am trying to get a formula which will automatically calculate.
Solved by K. L. in 26 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
15/06/2018 - 09:46
Hello, I understand that you would like to assign scores depending on the result of your prediction for the World Cup, right?
User
15/06/2018 - 09:46
yes please
Excelchat Expert
15/06/2018 - 09:47
Sure. We can help with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
User
15/06/2018 - 09:47
that's fine thanks
Excelchat Expert
15/06/2018 - 09:47
Go on with the sample data please.
User
15/06/2018 - 09:47
that was yesterdays results
User
15/06/2018 - 09:48
for the people who correctly guessed a win but the incorrect score 1 point
User
15/06/2018 - 09:48
but 3 if they had guessed 5-0
Excelchat Expert
15/06/2018 - 09:48
So row 2 are the players?
User
15/06/2018 - 09:48
yes
Excelchat Expert
15/06/2018 - 09:48
Row 3 are the predictions. H3, is the actual score?
User
15/06/2018 - 09:48
correct
Excelchat Expert
15/06/2018 - 09:48
Is that the Russia, Saudi game?
User
15/06/2018 - 09:48
yes
Excelchat Expert
15/06/2018 - 09:49
Okay, can you give me a sample output?
Excelchat Expert
15/06/2018 - 09:49
For instance, how much points does player 1 get?
User
15/06/2018 - 09:49
player 1 2 and 3 guessed correct result but wrong score so should get 1 point
User
15/06/2018 - 09:49
if they had got 5-0 correct would be 3 points
Excelchat Expert
15/06/2018 - 09:49
I see.
Excelchat Expert
15/06/2018 - 09:50
Incorrect prediction has no penalty but they don't get any points either?
User
15/06/2018 - 09:50
I could do the formula for the one game just didn't know how to make it for different results ie draw
User
15/06/2018 - 09:50
yes that right
Excelchat Expert
15/06/2018 - 09:50
So only 0,1 or 3 points are allowed?
User
15/06/2018 - 09:50
yes
Excelchat Expert
15/06/2018 - 09:50
Where do you want the result to be placed?
User
15/06/2018 - 09:50
column g
User
15/06/2018 - 09:50
row 3
User
15/06/2018 - 09:51
sorry no
Excelchat Expert
15/06/2018 - 09:51
I'm not sure how to fit all 5 player scores in one column though.
Excelchat Expert
15/06/2018 - 09:51
:)
User
15/06/2018 - 09:51
from column I-m
User
15/06/2018 - 09:51
adjacent to the scores
Excelchat Expert
15/06/2018 - 09:51
Do you think we can redesign the entire sheet?
User
15/06/2018 - 09:51
of course
User
15/06/2018 - 09:52
whatecer you think is best
Excelchat Expert
15/06/2018 - 09:52
The reason I'm asking is because writing 1-0 isn't really very helpful for Excel.
Excelchat Expert
15/06/2018 - 09:52
Excel can't calculate that properly as that is considered a text and not a numerical value.
Excelchat Expert
15/06/2018 - 09:52
We can, extract the scores but I think writing the scores in 2 different cells would be easier to maintain.
Excelchat Expert
15/06/2018 - 09:52
Give me a few minutes. I'll think of something.
User
15/06/2018 - 09:53
okay cool thanks
User
15/06/2018 - 09:56
ah I see now
User
15/06/2018 - 09:56
carry on but I see
User
15/06/2018 - 09:56
nice
Excelchat Expert
15/06/2018 - 09:56
Alright, just thinking about the best way to calculate.
Excelchat Expert
15/06/2018 - 10:01
Please extend the session once prompted.
User
15/06/2018 - 10:01
ok
Excelchat Expert
15/06/2018 - 10:08
I think that should do it.
Excelchat Expert
15/06/2018 - 10:08
Correctly guessing the outcome yields 1 point. Correctly guessing the cores, yields 3 points.
Excelchat Expert
15/06/2018 - 10:08
Everything else gets 0.
User
15/06/2018 - 10:09
that's great thanks
User
15/06/2018 - 10:09
is there a way to save the sheet?
Excelchat Expert
15/06/2018 - 10:09
You can disregard the "Draw" in the formula s that will never happen.
Excelchat Expert
15/06/2018 - 10:10
I just removed it. And yes, you can download the sheet using File > Download as Excel.
Excelchat Expert
15/06/2018 - 10:10
You can download this file as well.
[Uploaded an Excel file]
User
15/06/2018 - 10:10
i just tried that but it doesn't seem to be working
User
15/06/2018 - 10:11
okay cool that worked
User
15/06/2018 - 10:11
that's great thanks so much for your help
Excelchat Expert
15/06/2018 - 10:11
Glad to have helped!
Excelchat Expert
15/06/2018 - 10:11
Would there be anything else that I can help you with regards to the original question?
User
15/06/2018 - 10:11
no that's all thanks
Excelchat Expert
15/06/2018 - 10:11
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
15/06/2018 - 10:11
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
15/06/2018 - 10:11
Thank you for contacting Got It Pro. Have an awesome day!
User
15/06/2018 - 10:12
of course no problem
Excelchat Expert
15/06/2018 - 10:12
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User
15/06/2018 - 10:12
thanks
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
