Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If function
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If function
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If function
Soz wrote:
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((G2F2),(D2C2)),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((G2F2);(D2C2));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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If function
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 "Franz Verga" wrote: Soz wrote: 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((G2F2),(D2C2)),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((G2F2);(D2C2));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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |