# Formula/Macro to bring back value based on scoring value

#### mwjarvis

##### New Member
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

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

#### Fluff

##### MrExcel MVP, Moderator
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.

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

Replies
2
Views
89
Replies
6
Views
201
Replies
0
Views
184
Replies
1
Views
60
Replies
2
Views
374

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.

### Which adblocker are you using?

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

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