Hello,
I am running a soccer tipping competition (we are 34 players) whereby each
week each player sends me his tipps for that week. I have created a
spreadsheet where I enter their tipps and created a formula which calculates
what points that player is awarded depending on the result fo the game.
Points are awarded as follows:
- For tipping exact score 3 points.
- For tipping correct winner but with same goal difference, 2 points, eg.
tipp 3-1 and result was 2-0.
- For tipping correct winner but goal difference not the same, 1 point, eg.
tipp 3-1 and result was 1-0.
- For draws, exact score 3 points.
- For goal difference 1 up or down, 2 points, eg. tipp 2-2 and result was
1-1 or 3-3.
- For goal difference more than 1 up or down, 1 point, eg. tipp 2-2 and
result was 0-0 or 4-4.
The formula has one minor problem which I have not been able to find the
solution for. Most probably a simple solution but nevertheless I am
struggling. The minor problem relates ONLY to games which are €˜draws. The
formula does not calculate the €˜1 point result. It always awards 2 points.
Here is my formula: =
WENN(UND((F2=C2);(E2=B2));3;WENN(UND(ABS(F2-C2)<2;F2-E2=C2-B2);2;WENN(UND(ABS(F2-C2)1;F2-E2=C2-B2);2;WENN(UND((F2E2);(C2B2));1;WENN(UND((F2<E2) ;(C2<B2));1;0)))))))
Sorry I should have mentioned that I live in Germany and I use the German
version of Excel. Can someone help me please.
Best regards