View Full Version : Solved: Multiple logical tests
Blackie50
07-01-2010, 07:26 AM
Hi,
I have a footie scoring system as follows
Forecast Actual Points Awarded
C D F G
2-1 2-1 6 (a) full result correct
2-2 2-2 6 (b) full result correct
3-1 3-1 4 (c) result + 1 team score
1-2 1-2 4 (d) result + 1 team score
3-1 2-0 2 (e) result + no team score
1-3 0-4 2 (f) result + no team score
2-2 0-0 2 (g) result + no team score
3-1 1-3 0 (h) no result + no team score
I have devised following IF statements for above which seem to work fine individually but when I try and put together (tried several ways) I keep getting formula error message. Tried nesting and also using 'AND' and 'OR' after each IF. (but without the zeros (except last one)
IF(AND(C1=F1,D1=G1),6,0) (a & b)
IF(AND(C1=F1,D1<>G1),4,0) (c)
IF(AND(C1<>F1,D1=G1),4,0) (d)
IF(AND(C1<>F1,D1<>G1,C1>D1,F1>G1),2,0) (e)
IF(AND(C1<>F1,D1<>G1,C1<D1,F1<G1),2,0) (f)
IF(AND(C1<>F1,D1<>G1,C1=D1,F1=G1),2,0) (g)
Would like to put all together so one formula checks and awards the corresponding score.
Any help appreciated
thanks
Blackie50
07-01-2010, 07:28 AM
Sorry c & d examples should read
3-1 3-0
1-3 1-2
so result correct + 1 score
Bob Phillips
07-01-2010, 07:32 AM
Try
=(--OR(AND(C2>D2,F2>G2),AND(C2=D2,F2=G2),AND(C2<D2,F2<G2))--(C2=F2)--(D2=G2))*2
Blackie50
07-01-2010, 07:40 AM
Blimey - couldn't have come up with that in a few years!
Not even sure how it works - but it does
thanks for your help
regards
Jon
Bob Phillips
07-01-2010, 08:02 AM
It's quite straight-forward. It is based upon the fact that you get 2 points for a correct result, 2 more for a correct home score, and 2 more for a correct away score. So rather than determine whether the prediction is a 6 point prediction, with all possibilities of such, a 4 point or a 2 point, it just calculates each of those 3 parts.
This part
--OR(AND(C2>D2,F2>G2),AND(C2=D2,F2=G2),AND(C2<D2,F2<G2))
checks the result, based upon the logic that the result is true if the home score is greater than the away score for both results(AND(C2>D2,F2>G2)), or both are equal (AND(C2=D2,F2=G2)), or both are less than (AND(C2<D2,F2<G2)). This will return TRUE or FALSE, which is coerced to 1 or 0 with --.
This part
--(C2=F2)
checks that the home scores are the same and coerces it to 1 or 0 as before, whilst
--(D2=G2)
checks that the away scores are the same and coerces it to 1 or 0 as before.
Thus, this will return 3 values of 1 or 0 which are added in the formula and then the whole thing is multiplied by 2 as you are using 2, 4 and 6 as the points awarded.
Blackie50
07-01-2010, 08:41 AM
Thanks for previous explanation XLD.
I forgot one result
1-0 1-3 no result 1 team score = 1 point
0-1 4-1 same
How would this apply to the formula?
Also was thinking bout awarding 3 points rather than 2 for following
0-0 2-2 so predict draw but team scores wrong
many thanks
Jon
Bob Phillips
07-01-2010, 08:45 AM
Thinking about it a little more, I don't need --, + will coerce it just as well because TRUE+TRUE=2 and TRUE+FALSE=1 and so on.
So we can use
=(OR(AND(C2>D2,F2>G2),AND(C2=D2,F2=G2),AND(C2<D2,F2<G2))+(C2=F2)+(D2=G2))*2
Bob Phillips
07-01-2010, 08:56 AM
Thanks for previous explanation XLD.
I forgot one result
1-0 1-3 no result 1 team score = 1 point
0-1 4-1 same
How would this apply to the formula?
Also was thinking bout awarding 3 points rather than 2 for following
0-0 2-2 so predict draw but team scores wrong
many thanks
Jon
Jon,
I have merged the threads so as to keep context for anyone else reading it.
So we are now saying, result and 1 team score is 4 points, but no result and 1 team score is just one point?
Blackie50
07-01-2010, 09:41 AM
Hi Bob.
Yes -> result + 1 team score = 4
no result and one team score = 1
thanks
Jon
Bob Phillips
07-01-2010, 10:18 AM
This gets a bit more complex (although I am amazed you couldn't figure it outr after my clear and concise explanantion :devil2:)
=((OR(AND(C2>D2,F2>G2),AND(C2=D2,F2=G2),AND(C2<D2,F2<G2))+(C2=F2)+(D2=G2))*(OR(AND(C2>D2,F2>G2),AND(C2=D2,F2=G2),AND(C2<D2,F2<G2))+1))
On the other point, why would a 2-2 0-0 be worth 3 points but say 3-1 2-0 only be worth 2?
Blackie50
07-01-2010, 01:07 PM
Thanks for updated formula
I think I understood what you were saying on the first formula and I guess I could probably have had a stab at working the extra bit out (but I suspect not coming up with the right answer). As you said much more complex.
The idea behind awarding 3 points rather than 2 was that its harder to predict a draw than it is a home or away win e.g. if Man U play Blackpool at home you would expect at least a 2-0 win.
I'll try and pick your code to bits and see if I can come up with an answer
thanks again for your help
Jon
Bob Phillips
07-01-2010, 02:25 PM
It's okay Jon, just teasing.
I see your poin t about saying a draw for Blackpool at Man U is unexpected and so carries a premium, but in that case, why isn'y an away win for Blackppol at Man U, why is a Blackpool win at Man U getting the same points as a Man U win at Blackpool.
Anyway, while you ponder that, here is the latest formula
=((OR(AND(C2>D2,F2>G2),AND(C2=D2,F2=G2),AND(C2<D2,F2<G2))+(C2=F2)+(D2=G2))*(OR(AND(C2>D2,F2>G2),AND(C2=D2,F2=G2),AND(C2<D2,F2<G2))+1))+(AND(C2=D2,F2=G2,C2<>G2))
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.