Formula Help Needed . .

[J.Scargill]

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!

 

[sh1fty]

Hi,

This should work:

=IF(AND(C4=C3,E4=E3),3,IF(AND(C3>E3,C4>E4),1,IF(AND(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]

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]

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!C4>Sheet2!D4,Sheet1!C7>Sheet1!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

 

[Steve Dunn]

Untested, but should work based on your description:

=((Fixtures!C7>Fixtures!D7)*(B4>C4)+(Fixtures!C7<Fixtures!D7)*(B4<C4))+2*(B4=Fixtures!C7)*(C4=Fixtures!D7)

HTH
Steve D.

 

[J.Scargill]

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.

 

[Steve Dunn]

Untested, but should work based on your description:

=((Fixtures!C7>Fixtures!D7)*(B4>C4)+(Fixtures!C7<Fixtures!D7)*(B4<C4)+(Fixtures!C7=Fixtures!D7)*(B4=C4))+2*(B4=Fixtures!C7)*(C4=Fixtures!D7)

HTH
Steve D.

 

[J.Scargill]

Steve, that is fantastic! Works like a dream.

Thankyou very much.

 

[Steve Dunn]

You're welcome, glad to help.

 

[J.Scargill]

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]

=if((Fixtures!C7="")+(Fixtures!D7=""),"",((Fixtures!C7>Fixtures!D7)*(B4>C4)+(Fixtures!C7<Fixtures!D7)*(B4<C4)+(Fixtures!C7=Fixtures!D7)*(B4=C4))+2*(B4=Fixtures!C7)*(C4=Fixtures!D7))

 

[J.Scargill]

Again, thanks Steve.

=if((Fixtures!C7="")+(Fixtures!D7=""),"",((Fixtures!C7>Fixtures!D7)*(B4>C4)+(Fixtures!C7<Fixtures!D7)*(B4<C4)+(Fixtures!C7=Fixtures!D7)*(B4=C4))+2*(B4=Fixtures!C7)*(C4=Fixtures!D7))