Formula Help Needed . .
in [Functions]
|
Prev: how to remove page breaks from an existing document
Next: assign a macro to send email in lotus notes from Excel spreadsheet
From: J.Scargill on 6 May 2010 11:30 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!
From: sh1fty on 6 May 2010 12:09 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" 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!
From: J.Scargill on 7 May 2010 03:43 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(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" 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!
From: sh1fty on 7 May 2010 04:22 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 "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(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" 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!
From: Steve Dunn on 7 May 2010 05:20
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" <JScargill(a)discussions.microsoft.com> wrote in message news:3C0622AC-9E57-453C-A50F-61543765F313(a)microsoft.com... > 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! |