Solved: Multiple logical tests

[Blackie50]

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]

Sorry c & d examples should read

3-1 3-0
1-3 1-2

so result correct + 1 score

[Bob Phillips]

Try

=(--OR(AND(C2>D2,F2>G2),AND(C2=D2,F2=G2),AND(C2<D2,F2<G2))--(C2=F2)--(D2=G2))*2

[Blackie50]

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]

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]

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]

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]

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]

Hi Bob.

Yes -> result + 1 team score = 4
no result and one team score = 1

thanks

Jon

[Bob Phillips]

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]

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]

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