If function

[Soz]

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((F2>E2);(C2>B2));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

 

[Franz Verga]

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((F2>E2);(C2>B2));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 here:

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

 

[Soz]

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]

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

Try this formula:

=IF(AND((G2=D2),(F2=C2)),3,IF(AND(ABS(G2-D2)<2,G2-F2=D2-C2),2,IF(OR(AND((G2>F2),(D2>C2)),AND(ABS(G2-D2)>1,G2-F2=D2-C2),AND((G2<F2),(D2<C2))),1,0)))

or in German:

=WENN(UND((G2=D2);(F2=C2));3;WENN(UND(ABS(G2-D2)<2;G2-F2=D2-C2);2;WENN(ODER(UND((G2>F2);(D2>C2));UND(ABS(G2-D2)>1;G2-F2=D2-C2);UND((G2<F2);(D2<C2)));1;0)))


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Soz]

Hello,
As mentioned already someone was kind enough to give me a formula which did
the job, but your answer also appreciated which which works for the draw
scenario but seems not to work for other scenarios except for an exact
predition. Nevertheless thanks for your help.

BRgds
SOz