Hello,
In the meantime someone has assisted me but it would be interesting to hear
your thoughts on this matter.

For your info
B2 = actual score for home team
C2 = actual score for away team
E2 = predicted score of home team
F2 = predicted score of away team

thanks and best regards
Soz


"Franz Verga" wrote:

Soz wrote:
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.


Hi Soz,

first of language is not important, cause I have installed a free add-in
very useful to translate Excel formulas. The add-in is TranslateIT and you
can find he

http://members.chello.nl/jvolk/keepitcool/index.html


What you forgot to tell is what do you have in B2, C2, E2 and F2. If you
don't tell us what this cells mean it's quite impossible to debug your
formula...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy