Formula/Macro to bring back value based on scoring value

[mwjarvis]

Hi,

I'm setting up a competition at work for the upcoming Euros and looking to make my life a little bit easier when running it.

It's a score prediction game and the below are the scoring rules we're using:

3 points - Outcome (I.e. Score Prediction is 3 - 1 Turkey v Italy, the Full Time Result finishing 2 - 0 Turkey v Italy, this would result in 3 points as the person got the correct outcome in picking Turkey to Win).
5 points - Team Score Correct (I.e. Prediction is 3 - 1 Turkey v Italy, the Full Time Result finishing 3 - 0 Turkey v Italy, this would result in 5 points as the person got the correct outcome in picking Turkey to Win but also how many goals Turkey would win).
7 points - Correct Score (I.e. Score Prediction is 3 - 1 Turkey v Italy, the Full Time Result finishing 3 - 1 Turkey v Italy, this would result in 7 points as they've correctly picked the correct final score).
0 points - Incorrect outcome (I.e. Score Prediction is 3 - 1 Turkey v Italy, the Full Time Result finishing 0 - 2 Turkey v Italy, this would result in 0 points as they've incorrectly picked the correct final outcome).

Is there any quick way of doing a formula or macro which could populate the points column below?

Date	Time	Team1	Actual Score	Matt's score predictions	Points
Friday 11th June	20:00	Turkey v Italy	3 - 1	3 - 0
Saturday 12th June	14:00	Wales v Switzerland	2 - 1	2 - 1
Saturday 12th June	17:00	Denmark v Finland	1 - 0	4 - 3
Saturday 12th June	20:00	Belgium v Russia	0 - 0	2 - 0

Thanks in advance.

MW

 

[offthelip]

Assuming separate columns for the home and away actual score and predictions, ( I have assumed D / E and F/G) try this equation:

=OR((D2>E2)*(F2>G2),(D2<E2)*(F2<G2))*3+(D2=F2)*2+(E2=G2)*2

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula to bring back value based on scoring value
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[mwjarvis]

Assuming separate columns for the home and away actual score and predictions, ( I have assumed D / E and F/G) try this equation:

=OR((D2>E2)*(F2>G2),(D2<E2)*(F2<G2))*3+(D2=F2)*2+(E2=G2)*2

Brilliant, thanks a lot for that. Much appreciated.