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!

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!

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!

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!

Untested, but should work based on your description:

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

HTH
Steve D.


"J.Scargill" wrote in message
...
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!

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!

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!

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!

You're welcome, glad to help.

"J.Scargill" wrote in message
...
Steve, that is fantastic! Works like a dream.

Thankyou very much.

Steve,

Only minor problem I have now is that all predictions of a draw are
returning a 1 or a 3 because the cells they are pulling from are blank!

Can you suggest anything to cure this? It will cause a problem when I come
to populate the league table as it will count the 1s and 3s on games that
havent been played and invalidate the individuals score.

"Steve Dunn" wrote:

You're welcome, glad to help.

"J.Scargill" wrote in message
...
Steve, that is fantastic! Works like a dream.

Thankyou very much.

=if((Fixtures!C7="")+(Fixtures!D7=""),"",((Fixture s!C7Fixtures!D7)*(B4C4)+(Fixtures!C7<Fixtures!D7 )*(B4<C4)+(Fixtures!C7=Fixtures!D7)*(B4=C4))+2*(B4 =Fixtures!C7)*(C4=Fixtures!D7))


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

Only minor problem I have now is that all predictions of a draw are
returning a 1 or a 3 because the cells they are pulling from are blank!

Can you suggest anything to cure this? It will cause a problem when I come
to populate the league table as it will count the 1s and 3s on games that
havent been played and invalidate the individuals score.

"Steve Dunn" wrote:

You're welcome, glad to help.

"J.Scargill" wrote in message
...
Steve, that is fantastic! Works like a dream.

Thankyou very much.

Again, thanks Steve.

"Steve Dunn" wrote:

=if((Fixtures!C7="")+(Fixtures!D7=""),"",((Fixture s!C7Fixtures!D7)*(B4C4)+(Fixtures!C7<Fixtures!D7 )*(B4<C4)+(Fixtures!C7=Fixtures!D7)*(B4=C4))+2*(B4 =Fixtures!C7)*(C4=Fixtures!D7))


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

Only minor problem I have now is that all predictions of a draw are
returning a 1 or a 3 because the cells they are pulling from are blank!

Can you suggest anything to cure this? It will cause a problem when I come
to populate the league table as it will count the 1s and 3s on games that
havent been played and invalidate the individuals score.

"Steve Dunn" wrote:

You're welcome, glad to help.

"J.Scargill" wrote in message
...
Steve, that is fantastic! Works like a dream.

Thankyou very much.