Formula Help Needed . .
 

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!

Formula Help Needed . .
 

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!

Formula Help Needed . .
 

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!

Formula Help Needed . .
 

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!

Formula Help Needed . .
 

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!

Formula Help Needed . .
 

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!

Formula Help Needed . .
 

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!

Formula Help Needed . .
 

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!

Formula Help Needed . .
 

You're welcome, glad to help.

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

Thankyou very much.

Formula Help Needed . .
 

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.

Formula Help Needed . .
 

=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.

Formula Help Needed . .
 

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.