Anyone good with booleans?

[Samdy Gray]

I'm using Excel to work something out and I'm sure there's a simpler way of doing this formula, but can't seem to get my brain in gear:

Code:

=IF(AND($C$3>$D$3,$C7>$D7),2,IF(AND($C$3<$D$3,$C7<$D7),2,IF(AND($C$3=$D$3,$C7=$D7),2,0)))

[axs]

couldn't you just use an IF statement with the product of d3 minus c3 etc?

[Ralphy]

Why would you want to work that out?!

[donkey]

I'm using Excel to work something out and I'm sure there's a simpler way of doing this formula, but can't seem to get my brain in gear:

Code:

=IF(AND($C$3>$D$3,$C7>$D7),2,IF(AND($C$3<$D$3,$C7<$D7),2,IF(AND($C$3=$D$3,$C7=$D7),2,0)))

It's a monkey eating a banana.

[ron dodgers]

is it like COBOL where you would do an IF.. NOT.. OR jobby not sure of the syntax in Excel

[Samdy Gray]

Any IF statement is the same really, just the syntax is slightly different between programming languages. Like for example Excel has the format "IF [logic], [value if true], [value if false]" whereas say in Visual Basic it's "IF [logic] THEN [value if true] ELSE [value if false]".

The statement I put in my first post does work though, I'm just sure there's probably a more efficient way to code it, hence my question.

[Bushey Boy]

Geek, thats all

[axs]

Any IF statement is the same really, just the syntax is slightly different between programming languages. Like for example Excel has the format "IF [logic], [value if true], [value if false]" whereas say in Visual Basic it's "IF [logic] THEN [value if true] ELSE [value if false]".

The statement I put in my first post does work though, I'm just sure there's probably a more efficient way to code it, hence my question.

I can't think of any better ways to write it other than using an IF for the product as stated or a NOT might be easier, but without seeing what you're working with it's a bit tricky.

[Samdy Gray]

It's for the prediction league, trying to automate it in Excel to save me time. Trying to use the boolean for the 'correct outcome' scoring.

So for example, if the score was 3-0 and somebody predicted 2-0 I want it to add the value '2'. The '3' and the '0' are in seperate cells: C3 and D3. The predicted score is in C7 and D7.

But I've just seen a flaw in the code anway, so I think your product idea might be the only way that will work.

[axs]

with that as the way it is and someone predicted 2-1 and it finished 2-3, they would get points when they shouldn't? or is that the flaw you talked of?

[Samdy Gray]

That's the flaw I just saw, yes. So by doing the product one like this it should work (I think):

Code:

=IF(AND($C$3-$D$3=0,$C7-$D7=0),2,IF(AND($C$3-$D$3=<0,$C7-$D7=<0),2,IF(AND($C$3-$D$3=>0,$C7+$D7=>0),2,0)))

[axs]

yes, assuming it was 2 points for correct result then that works.

[@MacPhlea]

I have a prediction league thing already made up in excel that I have used for the last 8 years for world and euro championships. you can download it below (you may need to unprotect the worksheet first if yu want to make changes to the format)

http://www.comptonbassett.com/euro2004.xls

[Samdy Gray]

Top stuff, cheers.