Formula woes

shangar

New Member
Joined
Feb 7, 2016
Messages
2
Hi

I wonder if you could help. In an attempt to raise some funds for my football (soccer) team I'm looking at running a prediction competition during Euro 2016.

The idea is pretty basic, but I wish to use the power of Excel to minimise my admin duties. Unfortunately, my Excel knowledge doesn't extend far beyond the odd chart!

So, I’d be grateful if you could take a look at the following & propose how best to go about applying this to a spreadsheet.
Sheet1, will contain results of matches. This result will then populate columns D & E. Columns F & G will be populated by the actual prediction, see below –


ABCDEFGH
1ActualScorePredictedScorePoints
2FrancevRomania4231?
3

<tbody>
</tbody>

What I wish to do in column H is allocate points in relation to the predicted score, for example -
A correct scoreline (4 2) = 5 points
A correct result (3 1) = 2 points
An incorrect result (for example, 1 1, or 1 2) = 0 points

It is this formula that is well beyond my Excel skills.

Thanks in anticipation for entertaining my post.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi and welcome to the forum,

Perhaps try this in H2 and copy down:

Code:
=IF(
    AND(D2 = F2, E2 = G2),
    5,
    IF(
      SIGN(D2 - E2) = SIGN(F2 - G2),
      2,
      0))
 
Upvote 0
Thanks very much for the prompt reply, and the suggested formula. The formula is exactly what I'm looking for.

Have a great day.

Hi and welcome to the forum,

Perhaps try this in H2 and copy down:

Code:
=IF(
    AND(D2 = F2, E2 = G2),
    5,
    IF(
      SIGN(D2 - E2) = SIGN(F2 - G2),
      2,
      0))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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