S
Soz
Hi,
I have a small soccer competition running with abt 22
players and I have created an Excel spreadsheet for
keeping track of all the scores. I am trying to create
formulas to calculate the points that each player is
awarded depending on his prediction and the final result.
For example:
- 3 points for a correct result "exact",
ie. prediction 3-1, result 3-1.
- 2 points for predicting winner, not exact result but
correct goal difference, ie. prediction 1-0, result 2-1.
- 1 point for predicting winner,
ie. prediction 4-0, result 1-0
- 0 points for tipping wrong.
I have created a formula using the function IF and have
managed to ge the computer to give the 3 points and the 2
points and logically the 0 points but the 1 point is
causing me difficulties. Mainly because there are many
permitations.
The formula which I have is as follows but unfortunately
when it comes to calculating the 1 point it doesn't
always work.
=WENN(((E3=B3)*UND(F3=C3));
"3";WENN((B3-C3)=(E3-F3);
"2";WENN((B3>=E3)*UND(C3>=F3);"1";"0")))
Can anyone help?
Best regards
Soz
I have a small soccer competition running with abt 22
players and I have created an Excel spreadsheet for
keeping track of all the scores. I am trying to create
formulas to calculate the points that each player is
awarded depending on his prediction and the final result.
For example:
- 3 points for a correct result "exact",
ie. prediction 3-1, result 3-1.
- 2 points for predicting winner, not exact result but
correct goal difference, ie. prediction 1-0, result 2-1.
- 1 point for predicting winner,
ie. prediction 4-0, result 1-0
- 0 points for tipping wrong.
I have created a formula using the function IF and have
managed to ge the computer to give the 3 points and the 2
points and logically the 0 points but the 1 point is
causing me difficulties. Mainly because there are many
permitations.
The formula which I have is as follows but unfortunately
when it comes to calculating the 1 point it doesn't
always work.
=WENN(((E3=B3)*UND(F3=C3));
"3";WENN((B3-C3)=(E3-F3);
"2";WENN((B3>=E3)*UND(C3>=F3);"1";"0")))
Can anyone help?
Best regards
Soz