Matching cells

[mattyn]

Hi again All!I have a worksheet - column A is a list of actual football scores in the format digit.space.digit such as >1 1<.Column B is a list of score predictions, in the same format.Column C is looking for identical matches - i.e. where the score is an exact match and I use the following: -if(A1=B1,"1","0").I want column D to look for correct results (i.e draw, home win or away win) - in the above example, if Column B says 2 2 or 0 0 for instance, then to give me a 1.If column A says 1 0 for instance, and column B says 5 0, i want column D to give me a 1.If column A says 0 3 for instance, and column B says 0 1, i want column D to give me a 1.If column D does not match the draw/Home win/Away Win, I want column D to give me a 0.Hope this makes sense - can anyone advise and help please????many thanks in advance.Matt

 

[mattyn]

Hi again All!

I have a worksheet - column A is a list of actual football scores in the format digit.space.digit such as >1 1<.

Column B is a list of score predictions, in the same format.

Column C is looking for identical matches - i.e. where the score is an exact match and I use the following: -if(A1=B1,"1","0").

I want column D to look for correct results (i.e draw, home win or away win) - in the above example, if Column B says 2 2 or 0 0 for instance, then to give me a 1. If column A says 1 0 for instance, and column B says 5 0, i want column D to give me a 1. If column A says 0 3 for instance, and column B says 0 1, i want column D to give me a 1.

If column D does not match the draw/Home win/Away Win, I want column D to give me a 0.

Hope this makes sense - can anyone advise and help please????

many thanks in advance.

Matt


Edit: Bumped because of poor formatting above!!

 

[Peter_SSs]

...if Column B says 2 2 or 0 0 for instance, then to give me a 1. If column A says 1 0 for instance, and column B says 5 0, i want column D to give me a 1. If column A says 0 3 for instance, and column B says 0 1, i want column D to give me a 1.

That seems to me to be saying that whether it is a draw, home win or away win, column D would have a 1. I suspect that is not what you want so perhaps you could give us, say, 5-10 rows of sample data, the expected results and further explanation why they are the expected results. My signature block below has help on how to provide sample data that we can use to test with.

 

[mattyn]

Hi Peter/All

OK sample data - apologies for not doing this first time round!

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Actual score[/TD]
[TD]Predicted Score[/TD]
[TD]Correct Score[/TD]
[TD]Correct Result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2 2[/TD]
[TD]2 2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0 0[/TD]
[TD]1 1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1 1[/TD]
[TD]0 2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0 3[/TD]
[TD]0 1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1 2[/TD]
[TD]1 2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0 1[/TD]
[TD]2 2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1 0[/TD]
[TD]0 0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4 0[/TD]
[TD]2 1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3 1[/TD]
[TD]3 1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
getting a "1" in Col C I can do by =IF(B2=A2,"1","0").

how do I get the "1" or "0" in Col D?

I hope this now makes sense?

Matt

 

[Peter_SSs]

I can do by =IF(B2=A2,"1","0")

When dealing with numbers I would recommend not enclosing them in "". So instead
=IF(B2=A2,1,0)
Another way is shown below, along with a formula for "correct result".

Excel Workbook
	A	B	C	D
1	Actual score	Predicted Score	Correct Score	Correct Result
2	2 2	2 2	1	1
3	0 0	1 1	0	1
4	1 1	0 2	0	0
5	0 3	0 1	0	1
6	1 2	1 2	1	1
7	0 1	2 2	0	0
8	1 0	0 0	0	0
9	4 0	2 1	0	1
10	3 1	3 1	1	1
Check Scores

The above is based on your original information that the scores would all be single digits. If you think double-digit scores would be possible, the formula for "correct result" gets a bit more complex:
=--(SIGN(LEFT(A2,FIND(" ",A2))-REPLACE(A2,1,FIND(" ",A2),""))=SIGN(LEFT(B2,FIND(" ",B2))-REPLACE(B2,1,FIND(" ",B2),"")))

 

[mattyn]

Immense Peter - thank you so so much!

I will try this later and see if I can get it to work!

Cheers

Matt

 

[Peter_SSs]

I will try this later and see if I can get it to work!

Cheers. Look forward to hearing how it went.