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 7 May 2010 06:16 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!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 06:42 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" <JScargill(a)discussions.microsoft.com> wrote in message news:2C318AF8-5468-4EF6-B208-ED4A38DBA18F(a)microsoft.com... > 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!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: J.Scargill on 7 May 2010 06:55 Steve, that is fantastic! Works like a dream. Thankyou very much. "Steve Dunn" wrote: > 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" <JScargill(a)discussions.microsoft.com> wrote in message > news:2C318AF8-5468-4EF6-B208-ED4A38DBA18F(a)microsoft.com... > > 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!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 07:02 You're welcome, glad to help. "J.Scargill" <JScargill(a)discussions.microsoft.com> wrote in message news:15DF0596-D70E-4863-B0D4-6275F863A247(a)microsoft.com... > Steve, that is fantastic! Works like a dream. > > Thankyou very much. >
From: J.Scargill on 7 May 2010 08:41
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" <JScargill(a)discussions.microsoft.com> wrote in message > news:15DF0596-D70E-4863-B0D4-6275F863A247(a)microsoft.com... > > Steve, that is fantastic! Works like a dream. > > > > Thankyou very much. > > > |