Excel Formulas in league tables.

[Proud_Lyon]

Good evening everyone.

I am currently an amateur as far as excel is concerned. I was thinking of doing a project with regards to a football(soccer) league system where there would be a fixture list for the season; the results within the fixtures, the ranking system that gets updated according to the results in the fixture list. Finally, there is a league table that is automatically updated according to the maximum amount of points the team has with respects to a win and a draw; since a loss equals no points. I have had an example sent to me by a friend with a few formulas, and even though I am learning at the moment, it would be great to see if I have the gist of it. So, here is one particular formula:

For the league table, this is divided into the following from AG to AW:

Please Login or Register  to view this content.

For the first column under "P", the formula is thus:

Please Login or Register  to view this content.

Now, there are some other criteria too. These are Win, Lose, P1 and P2. These are under G5-J5 respectively. There are 424 lines of fixtures which is pretty heavy going to say the least. However, from what I can gather, the Formula itself is asking to countif, then it specifies a range(in this case, from I5 to I424 with it also being an absolute.) I guess I am trying to find out what it actually means.

Many thanks in advance for your help.

[rcm]

yes I think I understand somewhat. Could you please send an excel non sensitive sample of all the worksheets involved (a workbook)

[Proud_Lyon]

yes I think I understand somewhat. Could you please send an excel non sensitive sample of all the worksheets involved (a workbook)

Hello rcm.

Many thanks for a speed response. There are many other codes and it is to simply find out exactly what they mean and if they could be made easier i.e. having a named list instead of having certain cells being named etc. I will post the attachment. There are some hidden columns too at the top in between the fixture list and the league table. When you tab to the right of the league table to AG, you should find some more data (at the top of course.) For me, I guess it looks like a mess and in reality the table should be in a fixed position rather than copied/pasted all the way down. Many thanks for your help rcm.

[Proud_Lyon]

Hi guys. It is midnight here so, I will be logging off now. I wish you all the best for the rest of the day/evening and see you all soon tomorrow. Many thanks.

[Proud_Lyon]

Evening all. Just bumping this thread since I have a question with regards to a formula/function within the attached spreadsheet.

The formula is thus:

Please Login or Register  to view this content.

If someone could actually break it down step by step, I would be very grateful because then I would be able to understand it more clearly. Many thanks in advance.

[FDibbins]

Hi and welcome to the forum

I dont see that formula in your sample, but regarding your post # 5...
=IF(AND(D5<>"";E5<>"");IF(D5>E5;C5;IF(E5>D5;F5;"Draw"));"")

That is saying...
=IF both D5 AND E5 contain something...
........Then IF D5>E5, return the contents of C5, otherwise,
..................IF E5>D5, return F5 otherwise "Draw"))
.......othewise "")

It 1st tests to see if both D5 and E5 have something in them. If they dont, answer = ""
If they do then test which is bigger and either return C5 or DRAW

[Proud_Lyon]

Hi Ford. First of all, many thanks for the warm welcome Secondly, I completely forgot to post the reply whilst adding your reputation. As for your reply, if you open up the original file that I attached, you will find hidden columns between F and K, which I forgot to unhide and this should make it easier now to understand. Sorry about that.

With that in mind, here is the formula again, if I am correct with your statement:


=IF(AND(D5<>"";E5<>"");IF(D5>E5;C5;IF(E5>D5;F5;"Draw"));"")

That is saying...
=IF both D5 AND E5 contain something...
........Then IF D5>E5, return the contents of C5, otherwise,
..................IF E5>D5, return F5 otherwise "Draw"))
.......othewise "")

I believe that the quotations are in fact referring to the word "draw", since it is text, and not numbers. So,

IF both D5 AND E5 contain the same number...
Then IF D5>E5, return the contents of C5, otherwise,
IF E5>D5, return F5, otherwise "Draw"))


To break it down further:

Arsenal 1-3 Aston Villa. D5 is reference to the number 1, E5 is reference to the number 3. Therefore, if D5>E5, it will return the contents of C5 (Arsenal), otherwise F5 (Aston Villa.)

If E5 (3) is greater than D5(1), it will give F5 (Aston Villa) otherwise IF D5 <> E5 = "Draw." Thus the cell will be labled "Draw", and not any of the teams mentioned.
Am I right thus far? It is just so confusing with the IF(AND( functions.


I hope you have a nice day.

[FDibbins]

with this equation...
=IF(AND(D5<>"";E5<>"");IF(D5>E5;C5;IF(E5>D5;F5;"Draw"));"")
saying that "IF both D5 AND E5 contain the same number..." is not strictly correct. "AND(D5<>"";E5<>"")" translates to if D5 contains anything AND E5 contains anything (or...does not = nothing)

The rest of your comments are correct

And thanks for the rep, always appreciated