IF & AND Functions - Correct Score Predictor

Hi everyone,

New to this forum, hopefully I post in the right place. Here's what I am trying to do - set up a fantasy football type formula in Excel for predicting soccer scores.

If someone predicts 2-1, and the correct score in the match is 2-1, they will receive 5 points for CORRECT SCORE. If someone predicts 3-1 but the correct score is 2-1, they get 2 points for predicting CORRECT RESULT. If they get neither the score correct or the result correct - nil points I'm afraid.

Anyway, I am trying to combine the IF function and the AND function, to try and score this. Take 2-1 as the predicted score, and 2-1 as the correct score for now:

2 is in one predicted cell (A1), and 1 is in the other (B1) - the correct score 2 (C1) and the correct score 1 (D1) are in a further 2 cells. What I need to be able to do is say:

If A1=C1 and B1=D1, give the score as 5 points. It was my belief that this was done by: =IF(AND((A1=C1;B1=D1),5,0)), however I can't seem to get this working.

It may be just the fact I am doing this completely wrong, or there is a comma in somewhere, but any help on this matter would be appreciated muchly.

Cheers guys,
John-Boy

Thanks Hemesh - that worked! The forum I found before had the double brackets in, something which confused me but obviously not enough to confuse me to correct them!

ChemistB - thanks, trying this now. That was my next question, how to solve for 5,2 and 0. Baby steps I'm taking!

Parentheses placement

=IF(And(A1=C1,B1=D1),5,0)

This gives 5 points when both match, 0 otherwise. If you want to give 5 points when both match and 2 points when one matches, try this:

=IF(AND(A1=C1,B1=D1),5,IF(OR(A1=C1,B1=D1),2,0))

Hi Raymundus,

The formula from ChemistB actually works, assuming the first number is still bigger than the 2nd one, (2-1 for example), however if it were reversed 1-2, and the correct score was 1-3, this would in fact give a zero when it should be 2 points?

To be sure I understand, are you concerned with the numbers simply matching, or does the first number need to be bigger than the second to earn points? Meaning, if the score is 2-1, you want points, but if the score was 1-2 and the prediction was 1-2, you still want points?

What if the Prediction is 2-1 and the score is 0-1? They picked the wrong winner, but one number matches. Should points be awarded?

I'm off to the second job... If ChemistB picks up, great! If not, I'll check in tomorrow morning.

@Aardigspook

Lifesaver - thank you very much! It has worked a treat :D appreciate the help, everyone!

How about if we give just 2 points to predictions for draws but incorrect score.
For instance the final score is Russia 2 KSA 2, I have predicted 0-0 so i should just get 2 pts.
For draws I just want to give 5 pts for exact result or 2 points for calling the draw
there is no goal difference pts on draw.. what is the equation for this part/
Thanks