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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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")

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