Steve, that is fantastic! Works like a dream.

Thankyou very much.

"Steve Dunn" wrote:

Untested, but should work based on your description:

=((Fixtures!C7Fixtures!D7)*(B4C4)+(Fixtures!C7<F ixtures!D7)*(B4<C4)+(Fixtures!C7=Fixtures!D7)*(B4= C4))+2*(B4=Fixtures!C7)*(C4=Fixtures!D7)

HTH
Steve D.



"J.Scargill" wrote in message
...
Hi,

We are getting closer! The only issue I now have is that if the outcome is
a
draw (eg 0-0) and the prediction is also a draw but not an exact match (eg
1-1), it returns a 0 but should be a 1.

Can this be included in your latest formula??

Thanks again for all your help, much appreciated.

"sh1fty" wrote:

Hi,
Yes I can see where I made in error in that formula,

I have tried to replicate your setup as closely as possible, and the
following looks to work:

=IF(AND(C4=Sheet1!C7,Sheet1!D7=Sheet2!D4),3,IF(AND (Sheet2!C4Sheet2!D4,Sheet1!C7Sheet1!D7),1,IF(AND (Sheet2!C4<Sheet2!D4,Sheet1!C7<Sheet1!D7),1,0)))

You might need to just make sure that the cell references match up when
you
enter this into your workbook.

Let me know if this helps, and if it does, please click the 'Yes' below!

Thanks


"J.Scargill" wrote:

Hi sh1fty,

Thanks for your reply.

I have tried the suggested formula and it works if the prediction
matches
the actual but returns 0 if the prediction matched the outcome.

The 2 sheets concerned look like this;

Sheet 1 (Fixtures)-

Col B Col C Col D Col E
7 S Africa - - Mexico
8 Uruguay - - France
9 S Africa - - Uruguay
10 France - - Mexico
11 Mexico - - Uruguay
12 France - - S Africa

Sheet 2 (Precitions)-

Col B Col C Col D
4 1 0
5 1 2
6 1 1
7 1 0
8 0 0
9 1 1

To test your formual I put 1 and 0 into cells C7 and D7 on Sheet 1 and
this
correctly returned a 3 into Col D of Sheet 2. I then tried putting a 1
and a
3 into C8 and D8 but this incorrectly returns a 0 in Col D of Sheet 2
rather
than a 1.

Any ideas??

"sh1fty" wrote:

Hi,

This should work:

=IF(AND(C4=C3,E4=E3),3,IF(AND(C3E3,C4E4),1,IF(AN D(C3<E3,C4<E4),1,0)))

obviously you would have to match up the cells referred to with the
cells
holding the score values...

Hope it helps

"J.Scargill" wrote:

Hi all,

Hope you are all well.

To boost staff morale at my work, I am trying to come up with a
World Cup
2010 Predictions Competition.

I have 3 worksheets - Sheet 1 with all the fixtures from the group
games on
where I will input the actual result; Sheet 2 with the individuals
predictions and Sheet 3 is going to be my League Table.

My points system is 3pts for an exact match and 1pt for predicting
the
correct outcome of a fixture.

In Sheet 2 I have the formula
=IF((B4=Fixtures!C7)*(Predictions!C4=Fixtures!D7), 3,0) to calculate
if the
prediction is worth 3pts. But what formula do I use to calculate if
the
prediction is correct in terms of the outcome only

ie Prediction - Germany 2 France 1
Actual - Germany 1 France 0

Above should be worth 1pt.

Hope you can help!