Formula/Macro to bring back value based on scoring value

mwjarvis

New Member
Joined
Apr 12, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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?

DateTimeTeam1Actual ScoreMatt's score predictionsPoints
Friday 11th June20:00Turkey v Italy3 - 13 - 0
Saturday 12th June14:00Wales v Switzerland2 - 12 - 1
Saturday 12th June17:00Denmark v Finland1 - 04 - 3
Saturday 12th June20:00Belgium v Russia0 - 02 - 0

Thanks in advance.

MW
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,141
Office Version
  1. 2010
Platform
  1. Windows
Assuming separate columns for the home and away actual score and predictions, ( I have assumed D / E and F/G) try this equation:
Excel Formula:
=OR((D2>E2)*(F2>G2),(D2<E2)*(F2<G2))*3+(D2=F2)*2+(E2=G2)*2
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,617
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 12, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Assuming separate columns for the home and away actual score and predictions, ( I have assumed D / E and F/G) try this equation:
Excel Formula:
=OR((D2>E2)*(F2>G2),(D2<E2)*(F2<G2))*3+(D2=F2)*2+(E2=G2)*2

Brilliant, thanks a lot for that. Much appreciated.
 

Forum statistics

Threads
1,147,571
Messages
5,741,887
Members
423,693
Latest member
Excelquestion35

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top