-1

I started programming just a few weeks ago, got a grasp on the concepts and been evolving on it, since I have certain activities with spreadsheets, I decided to take it to the next level and got stuck on how to make a formula.

I'm manually gathering all the data and I need to keep it as it is, since it's important for the analysis to know if a team is playing at home or not.

Right now, I have something like this it would be A1, B1, C1 and D1. For example:

  | Newcastle | 1 | 2 | Liverpool 

I'm analyzing one club at time. So I need to know if the team on A1 or D1 is the team that I'm analyzing, then I need it to check how many points this team got from the match, so it needs to recognize what the result on B1/C1 is, and which team owns the result. The team on A1 owns the result on B1, and the team on D1 owns the result on C1.

I hope it's enough to enlight whoever can help me.

Maybe there are better functions to use than IF like I said in the title, if you can point it out, it would be also great. Right now I'm trying to work with nested IF functions.

3
  • I assume you're talking about Google Spreadsheets, so I've tagged your question as such. If not, please clarify.
    – ale
    Feb 20, 2014 at 2:08
  • Thank you for putting it into the right place, I couldn't find the right tag at the time. And I said it wrong, it would be A1 B1 C1 D1, not A1 A2 A3 A4. They are not in brackets, I just use them to graphically refer the cells. Yes, the numbers are the results of the matches. For example: Newcastle 1 2 Liverpool, ff the team that I'm analyzing is Liverpool, I want E1 to tell me if Liverpool won, draw or lost the match. So I need it to recognize who I am calculating the result. I discover yesterday how to nest if statements as if/else so I'm trying to do it now. Feb 20, 2014 at 16:56
  • But of course, any function or way to solve this - or even any input or direction on how I could solve it - will be appreciated. :) Feb 20, 2014 at 16:57

1 Answer 1

0

Please try:

=if(B1=C1;1;if(B1>C1;0;3)) 

IF the team to be analysed is in F1 then in E1 and copied down:

if(and(F$1=A1,B1>C1),3,if(and(or(F$1=A1,F$1=D1),C1=B1),1,if(and(F$1=D1,C1>B1),3,if(and(F$1=A1,B1>C1),3,0))))  

might suit. It is the nature of spreadsheets that locations (cell references) are very significant and with the limited information in the question difficult to optimise what appears an ugly formula.

14
  • For example: I'm calculating the results for just one team. This team played 38 matches along the season. This team is located when playing Home on A1, when it's playing away, it is located on D1. Unfortunately, your formula just gives me the right result if the team is playing away. I'll play with a bit though to see if I can combine something to achieve what I want. Thank you! Feb 20, 2014 at 18:37
  • The logic is, if "TeamBeingAnalyzed" is playing at Home, then calculate the result as giving 3 points if B1 > C1, if B1 < C1, then 0 points. But if "TeamBeingAnalyzed" is playing Away, then calculate the result as giving 3 points if C1 > B1, if C1 < B1, 0 points. For both statements if B1 = C1, 1 point. Feb 20, 2014 at 18:51
  • That's is the problem, pnuts. I could just manually change all of the 2280 matches so the team being analyzed is on A1, it would be easy to calculate the points and forget the formula, but what if then I want to calculate the same amount for 21 seasons? I would have to change it manually almost 8000 times, What If I want to calculate 100 seasons? And so on. Other way of saying it is: from where I'm gathering the data, the team being analyzed is in A1 when home and D1 when Away. And I need the formula to recognize it. Hope it helps. Thank you. Feb 20, 2014 at 18:56
  • Just seen the new formula, and it still didn't recognize the right result. Yes, the position of the string in the cell reference is crucial for the output. Using the example above, let's say that I'm analyzing "Newcastle", so if A1 is Newcastle, B1 is Newcastle goal, if Newcastle Goal is < than the other team goal, in C1, then = 0 points, if >, 3 points. If A1 is "<>Newcastle", the opposite. Maybe is it clear now? Thank you very much for the help so far! :) Feb 20, 2014 at 19:59
  • Ok, long explanation then. :) What I'm doing is analyzing the results of the first seven teams in this season on the Premier League. I want to compare their goals for, goals against and how in the past six seasons, the seven better placed teams played in their last 12 matches of each season, so I'm copying the data from Wikipedia and it gives me tables like Newcastle 1 2 Liverpool (A1 to D1), Liverpool 1 0 Chelsea (A2 to D2 and so on), for each team, is a different spreadsheet, and I'm doing one by one. [to continue] Feb 20, 2014 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.