| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
I'm unsure if this is do-able, but I know some of you will relish the
challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
=((A2B2)*(C2D2))*3+(A2=C2)+(B2=D2)
A2=Actual Home B2=Actual way C2=Predicted Home D2=Predicted Away HTH "JockW" wrote: I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
There's probably a more elegant solution out there, but this should work:
=IF(A2=$A$1,1,0)+IF(B2=$B$1,1,0)+IF(OR(AND(A2B2,$ A$1$B$1),AND(A2<B2,$A$2<$B$2),AND(A2=B2,$A$1=$B$1 )),3) This assumes your actual score is in A1 and B1, with your 1st guess scores in A2 and B2. HTH, Elkar "JockW" wrote: I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
If the actual result is in cells B2 and C2 (home & away), and the
predictions from your contestants are in the same columns in rows 4 onwards, then in D4 you can put the formula =3*(SIGN(B4-C4)=SIGN(B$2-C$2))+(B4=B$2)+(C4=C$2), and copy down as required. -- David Biddulph "JockW" wrote in message ... I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
For all correct results:
=(OR((A2B2)*(C2D2),(B2A2)*(D2C2),(A2=B2)*(C2=D 2))*3+(A2=C2)+(B2=D2)) "Toppers" wrote: =((A2B2)*(C2D2))*3+(A2=C2)+(B2=D2) A2=Actual Home B2=Actual way C2=Predicted Home D2=Predicted Away HTH "JockW" wrote: I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
Oops, just noticed a typo. Try this instead:
=IF(A2=$A$1,1,0)+IF(B2=$B$1,1,0)+IF(OR(AND(A2B2,$ A$1$B$1),AND(A2<B2,$A$1<$B$1),AND(A2=B2,$A$1=$B$1 )),3) HTH, Elkar "Elkar" wrote: There's probably a more elegant solution out there, but this should work: =IF(A2=$A$1,1,0)+IF(B2=$B$1,1,0)+IF(OR(AND(A2B2,$ A$1$B$1),AND(A2<B2,$A$2<$B$2),AND(A2=B2,$A$1=$B$1 )),3) This assumes your actual score is in A1 and B1, with your 1st guess scores in A2 and B2. HTH, Elkar "JockW" wrote: I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
See this screencap:
http://img119.imageshack.us/img119/5356/scoresdb9.jpg -- Biff Microsoft Excel MVP "JockW" wrote in message ... I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
This doesn't give the 3 points for correct predicttion of a draw?
-- David Biddulph "Toppers" wrote in message ... =((A2B2)*(C2D2))*3+(A2=C2)+(B2=D2) A2=Actual Home B2=Actual way C2=Predicted Home D2=Predicted Away HTH "JockW" wrote: I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
In the formula, you can do without the 1* and still get the correct result.
-- Biff Microsoft Excel MVP "T. Valko" wrote in message ... See this screencap: http://img119.imageshack.us/img119/5356/scoresdb9.jpg -- Biff Microsoft Excel MVP "JockW" wrote in message ... I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
Wow!
I'm overwhelmed guys. Many thanks for all your input on this, it's very much appreciated. -- tia "JockW" wrote: I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
I don't get it, but it works....
thanks. "David Biddulph" wrote: If the actual result is in cells B2 and C2 (home & away), and the predictions from your contestants are in the same columns in rows 4 onwards, then in D4 you can put the formula =3*(SIGN(B4-C4)=SIGN(B$2-C$2))+(B4=B$2)+(C4=C$2), and copy down as required. -- David Biddulph "JockW" wrote in message ... I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
Easy. When used in a calculation, a logical value of TRUE will count as 1,
and a FALSE counts as zero. -- David Biddulph "JockW" wrote in message ... I don't get it, but it works.... thanks. "David Biddulph" wrote: If the actual result is in cells B2 and C2 (home & away), and the predictions from your contestants are in the same columns in rows 4 onwards, then in D4 you can put the formula =3*(SIGN(B4-C4)=SIGN(B$2-C$2))+(B4=B$2)+(C4=C$2), and copy down as required. -- David Biddulph "JockW" wrote in message ... I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
Thanks for the update
-- "T. Valko" wrote: In the formula, you can do without the 1* and still get the correct result. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... See this screencap: http://img119.imageshack.us/img119/5356/scoresdb9.jpg -- Biff Microsoft Excel MVP "JockW" wrote in message ... I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
=IF(OR(AND($A$2$B$2,A4B4),AND($A$2<$B$2,A4<B4),A ND($A$2=$B$2,A4=B4)),3,0)+--($A$2=A4)+--($B$2=B4)
Assume Actual Home and Away scores are an A2 and B2 and predicted scores are in A4 and B4 down Regards Trevor "JockW" wrote in message ... I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
Thanks for the feedback!
-- Biff Microsoft Excel MVP "JockW" wrote in message ... Thanks for the update -- "T. Valko" wrote: In the formula, you can do without the 1* and still get the correct result. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... See this screencap: http://img119.imageshack.us/img119/5356/scoresdb9.jpg -- Biff Microsoft Excel MVP "JockW" wrote in message ... I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
Thanks Trevor, brilliant up to a point.
A4 and B4 can be absolutely anything (I.E.Z and Z) and the formula gives 3 points if the actual score was a draw. I discovered this when one entry had no score entered for a particular match (which was a draw) and the formula returned 3 for a game with no prediction. Is there a work around? -- Traa Dy Liooar Jock "Trevor Shuttleworth" wrote: =IF(OR(AND($A$2$B$2,A4B4),AND($A$2<$B$2,A4<B4),A ND($A$2=$B$2,A4=B4)),3,0)+--($A$2=A4)+--($B$2=B4) Assume Actual Home and Away scores are an A2 and B2 and predicted scores are in A4 and B4 down Regards Trevor "JockW" wrote in message ... I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
You said there'd be numbers ...
OK, users can't be trusted. Try: =IF(OR(NOT(ISNUMBER(A10)),NOT(ISNUMBER(B10))),"nul l point",IF(OR(AND($A$2$B$2,A10B10),AND($A$2<$B$2, A10<B10),AND($A$2=$B$2,A10=B10)),3,0)+--($A$2=A10)+--($B$2=B10)) Change the text to meet your requirements ... "cheat" maybe ? Did the other solutions have similar outcomes when you forecast rubbish ? Regards Trevor "Jock" wrote in message ... Thanks Trevor, brilliant up to a point. A4 and B4 can be absolutely anything (I.E.Z and Z) and the formula gives 3 points if the actual score was a draw. I discovered this when one entry had no score entered for a particular match (which was a draw) and the formula returned 3 for a game with no prediction. Is there a work around? -- Traa Dy Liooar Jock "Trevor Shuttleworth" wrote: =IF(OR(AND($A$2$B$2,A4B4),AND($A$2<$B$2,A4<B4),A ND($A$2=$B$2,A4=B4)),3,0)+--($A$2=A4)+--($B$2=B4) Assume Actual Home and Away scores are an A2 and B2 and predicted scores are in A4 and B4 down Regards Trevor "JockW" wrote in message ... I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
"Trevor Shuttleworth" wrote...
.... OK, users can't be trusted. The fundamental rule of application programming from which all good UI coding flows. Try: =IF(OR(NOT(ISNUMBER(A10)),NOT(ISNUMBER(B10))),"nu ll point", IF(OR(AND($A$2$B$2,A10B10),AND($A$2<$B$2,A10<B1 0), AND($A$2=$B$2,A10=B10)),3,0)+--($A$2=A10)+--($B$2=B10)) +-- bits unnecessary, + alone would be sufficient. However, too long. =IF(COUNT(A$2:B$2,A10:B10)=4,3*(SIGN(A$2-B$2)=SIGN(A10-B10)) +(A10=A$2)+(B10=B$2),"TBD") |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, AND, BUT and ON-THE-OTHER-HAND formula :)
Hi Trevor,
Thanks for getting back. All solutions supplied (except your modified one and Harlans - neither tested) had the exactly the same outcome if non numeric characters (and even if cells were left blank) were used in that, because the formula took <blank and <blank as being the same, it awarded 3 points. If no actual score was entered, then 5 points were clocked up as all criteria were met. I tried to get around the issue by putting =IF( at the beginning so it ignored blank cells. This worked up to a point. I finally ran with a slightly modified version of David Biddulphs proposal: =(IF(AND(F7="",G7=""),"",3*(SIGN(F7-G7)=SIGN($D7-$E7))+(F7=$D7)+(G7=$E7))) Thank you all for your time and effort - I just need to predict the corect scores now so I can retire.....lol -- tia "Trevor Shuttleworth" wrote: You said there'd be numbers ... OK, users can't be trusted. Try: =IF(OR(NOT(ISNUMBER(A10)),NOT(ISNUMBER(B10))),"nul l point",IF(OR(AND($A$2$B$2,A10B10),AND($A$2<$B$2, A10<B10),AND($A$2=$B$2,A10=B10)),3,0)+--($A$2=A10)+--($B$2=B10)) Change the text to meet your requirements ... "cheat" maybe ? Did the other solutions have similar outcomes when you forecast rubbish ? Regards Trevor "Jock" wrote in message ... Thanks Trevor, brilliant up to a point. A4 and B4 can be absolutely anything (I.E.Z and Z) and the formula gives 3 points if the actual score was a draw. I discovered this when one entry had no score entered for a particular match (which was a draw) and the formula returned 3 for a game with no prediction. Is there a work around? -- Traa Dy Liooar Jock "Trevor Shuttleworth" wrote: =IF(OR(AND($A$2$B$2,A4B4),AND($A$2<$B$2,A4<B4),A ND($A$2=$B$2,A4=B4)),3,0)+--($A$2=A4)+--($B$2=B4) Assume Actual Home and Away scores are an A2 and B2 and predicted scores are in A4 and B4 down Regards Trevor "JockW" wrote in message ... I'm unsure if this is do-able, but I know some of you will relish the challenge.... I'm creating a spreadsheet showing points gained from predicting soccer scores. For the correct result (ie home win, away win or draw) - 3 points, For getting home team score correct - 1 point, For getting away team score correct - 1 point. Therefore, the max possible points per game would be 5. So, for instance: Home Away 3 1 john's prediction 2 1 4 points - 3 for the result and 1 for the correct away score Jane's prediction 1 0 3 points for the correct result Jack's prediction 0 3 0 points I think there may be too many variables but any/all help greatly appreciated. -- tia |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why hand pointer in one and not the other | Excel Discussion (Misc queries) | |||
| Can someone give me a hand with this formula please? | Excel Worksheet Functions | |||
| Icon is a hand permanently | Excel Worksheet Functions | |||
| y-axis moves from the left hand side to the right hand side | Excel Discussion (Misc queries) | |||
| y-axis moves from the left hand side to the right hand side! | Charts and Charting in Excel | |||
Linear Mode
