Function IF.

[Soz]

Need someones help please.

I am running a football (soccer) league for friends and I am using Excel to
run it.
I have one spreadsheet where I enter the predictions and I have created a
formula that will calculate the points each player earns depending on the
actual score and the predicted score.
Here is the formula, sorry it is in German but I live in Germany and I have
a German laptop -
=WENN(ISTLEER(B2);"";WENN(ISTLEER(E2);"";WENN(UND((F2=C2);(E2=B2));3;WENN(F2-E2=C2-B2;2;WENN(UND((F2>E2);(C2>B2));1;WENN(UND((F2<E2);(C2<B2));1;0))))))

WENN means IF
ISTLEER means EMPTY
UND means AND
B2 and C2 have the actual scores and E2 and F2 have the predicted scores.

As you can see for predicting the exact score you earn 3 points for
predicting the winner and having the same goal difference 2 points and for
just predicting the winner 1 point. In case of a draw it is either 3 points
for the exact score or 2 points.

Now to my question, for the draw in case the predicted score is the same as
the actual then they should get 3 points, but in case the predicted score is
more than 1 goal away (up or down) then the should get only 1 point, eg.
actual score
3-3 predicted score 1-1, points earned 1 but if the predicted score is 2-2
then they should earn 2 points.

Very sorry it is so long a story but hope someone can help me.

Best regards
Soz.

 

[duane]

in english

=IF(OR(ISBLANK(B2),ISBLANK(E2)),"",IF(AND((F2=C2),(E2=B2)),3,IF(AND(ABS(F2-C2)<2,F2-E2=C2-B2),2,IF(AND(ABS(F2-C2)>1,F2-E2=C2-B2),1,IF(AND((F2>E2),(C2>B2)),1,IF(AND((F2<E2),(C2<B2)),1,0))))))