LinkBack URL
About LinkBacks
Hi, Can somebody have a quick look at this please. The table on the managers page should be sorting highest to lowest from column L (then K) but it's not and I can't see what I've got wrong.
Many thanks.
Hi, Can somebody have a quick look at this please. The table on the managers page should be sorting highest to lowest from column L (then K) but it's not and I can't see what I've got wrong.
Many thanks.
Last edited by Marvo; 02-16-2021 at 08:41 AM.
What do you mean by 'should be'? What have you done to effect the sort?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
I don't know. I thought I'd done it correctly, 1st place should be Jack Jennings as he has a perfect record of 3 points per game. Then Herbert Chapman with 1.708. It's obviously not sorting on high to low Column L, I don't know why.
But it's sorting on column A, which is what you've asked it to do.
So, what is the problem? Is this about your calculations or about Excel sorting on column A?
Yes, I've obviously got something wrong in the formula in Column B because the number given to Jack Jennings should be the highest. That's what I don't understand, I cant see the error. Be something simple I bet.
OK, probably, but as I have no idea what the logic is (and I'm not about to start trying to back engineer your failing formula), I can't help right now.
Please lay out in WORDS the logic that you wish to apply and tell us which should be the top five, in what order and why.
The logic. The table is the records of managers of Northampton Town FC throughout their history. I want the table sorted on their success rate, average points per game, shown in Column L.
Column K is used as a tiebreaker should any manager be level.
The top 5 should be (High to low)
Jack Jennings 3
Herbert Chapman 1.7
Colin Calderwood 1.69
Chris Wilder 1.67
Jack Tresadern 1.58
At the bottom 4 managers have a total of 0, so they are being sorted by Column K and that is working correctly.
I think you have been seriously over thinking this!
A4 copied down:
=LARGE($L$4:$L$53,ROW(A1))
B4 copied down:
=L4
That's done it. Lovely.
All these VLookup formulas were done for me over a decade ago so when I make new tables I try to adapt from previous ones. I'm now guessing I've lots of tables that were "seriously over thought".
many thanks.
I've just realised, that's no longer using column K as a "tiebreaker" and at the bottom it's listing the same manager 4 times. So I'm back to square one.
OK - I'll have a look at that, but no, you are not back at square one.
I think the simple issue is Ali, why is the lookup formula in B4
=L4/100+K4/100^2+COLUMN(C4)/100^3-ROW(B4)/100^4
producing a higher number than the formula in B16
=L16/100+K16/100^2+COLUMN(C16)/100^3-ROW(B16)/100^4
when L4 = 1.708
and L16 = 3
In A4:
=LARGE($B$4:$B$53,ROW(A1))
In B4:
=L4+(K4/10000000000000000)
Because they obviously aren't doing what you think they should be doing, and given there's a simpler solution, I'm not going to be working out why they don't work. Sorry!I think the simple issue is Ali, why ...
Last edited by AliGW; 02-16-2021 at 08:41 AM.
That's done it this time. Perfect. Thank you.
A general question if you don't mind? What is the reason behind all those zero's in B4?
Look carefully:
=L4+(K4/ 10000000000000000)
I am dividing K4 by a large number so that it returns a small fraction. I don't want it returning something larger as it would skew the first line rankings on the average points column.
Ah, okay. Thank you. I thought that the formula always used the first part and only went to the second if required. I didn't realise the second part could affect the first part.
Many thanks again.
That's correct, but if I allowed it just to add the GD score, then the overall ranking would be increased by +/- 1, and then you'd have your final 4 all over the shop. The tie-breaker has to be a very small fraction: say the average score that is tied is 1.888934: I want the breaker to be somewhere between 1.888934000001 and 1.888934999999. Does this clarify it?
To be honest, no, I'm afraid this is all above my pay grade. To put it into terms I understand, you have two result. Leeds beat York 1-0. Stoke beat Crewe 2-0. So Leeds and Stoke both have 3 points so to decide who goes top we go to goal difference and Stoke have +2 to Leeds +1 so they are top. If your first port of call is points to decide who is first or second, I don't see how the goal difference part of the formula could or should affect the overall ranking?
It doesn't matter though, you say it does and that's good enough for me. I'm all done again.
Because YOU said this:
I thought it perfectly reasonable, therefore, to use column K (the goal difference column) as the tie-breaker ...I've just realised, that's no longer using column K as a "tiebreaker" and at the bottom it's listing the same manager 4 times. So I'm back to square one.
Your first port of call is the average points. If the average points are the same, then you need a tie-breaker - you informed me that you expected the goal difference to be used as this. I don't understand what it is that you aren't getting. This is not complicated maths - I'm no mathematician (only a C at O Level).
It IS important that you understand, otherwise you will always be reliant on others to fix things for you.
Last edited by AliGW; 02-16-2021 at 09:11 AM.
Everything you say there is correct. But if the first criteria isn't the same, why would the second criteria affect it? That's what I don't understand.
You said "then you'd have your final 4 all over the shop" if we didn't use a fraction.
Incidentally the guy who originally set it all up used 100^2 and then 100^3 (and so on) to establish the criteria to take president. Is that the same thing as using all those zero's?
It is the same principle, but as you know, his formulae were not working, so let's forget about them now.
Let me try again.
Excel 2016 (Windows) 32 bit
Jeff Larry Gordon Hal
Sheet: Sheet1
I have split the tie between the final two managers, whose average score is the same, by adding their goal difference scores divided by 10000, before ranking them. Does this illustrate the point?
In practice you'd use a larger number (e.g. 100000000000000000000000000) to avoid affecting the average score through rounding up.
Excel 2016 (Windows) 32 bit
Jeff Larry Gordon Hal
Sheet: Sheet1
Last edited by AliGW; 02-16-2021 at 09:41 AM.
In fairness, his formula were/are working, it's just when I went to transpose them on to another table, something I've done with success countless times before, it didn't work this time. It will be something incredibly simple but I couldn't spot it and that's why I posted originally.
OK - well, all I am trying to do is explain why my solution works, not what you did to cause his to fail.
I hope that's enough for you. Sorry, but I can't be of any further help.
Last edited by AliGW; 02-16-2021 at 09:47 AM.
That's okay, you've helped me enormously.
I think I do get it now but why do we add the goal difference to the points, they are completely different criteria? I'd have thought it would have been a separate equation only to be used in the event of the first equation resulting in an exact tie. Like an IF statement or something you'd use in conditional formatting.
be like saying to somebody, go to the chest of drawers in the bedroom and in the top drawer you should find what you're after. However if it's not in there then try the second drawer?
Any I digress. Its all very interesting. My problem is as it has always been that I work on these things for a time, then go months, even years before I have to delve into the workings of them again and by that time I've simply forgotten how I did it in the first place and have to start again. I do solve my own problems lots of times now but when I can't I'm stumped. I think it's my age.
Many thanks again.
OK - here's a different approach that you might understand better:
Excel 2016 (Windows) 32 bit
Jeff Hal Simon Larry =RANK(F5,$F$5:$F$8,1) =E5+RANK(F5,$F$5:$F$8,1)/100000 =RANK(H5,$H$5:$H$8) =RANK(E5+RANK(F5,$F$5:$F$8,1)/100000,$H$5:$H$8)
Sheet: Sheet1
For this approach in your workbook, you'd need this in B4:
=L4+RANK(K4,$K$4:$K$53,1)/100000000000000
Last edited by AliGW; 02-16-2021 at 10:16 AM.
I get that. You're awarding a higher rank/score to the person with the better goal difference, then adding that to their average. I understand.
So, give me an example as above of how the GD ranking could adversely affect the actual final ranking which you referred to earlier when you said "then you'd have your final 4 all over the shop".
That's the bit I don't get.
It's to do with dividing it down to a small enough number, not the score itself. Consider this:
Excel 2016 (Windows) 32 bit
Jeff Hal Simon Larry Gordon
Sheet: Sheet1
Gordon should be top dog, but because I only divided the GD rank by 10, the calculations have left him third (yellow). Look at the green calculations far right - they are correct.
Last edited by AliGW; 02-16-2021 at 10:44 AM.
I did as you asked, but I see you've gone offline. Hopefully you'll see this at some point.
Yep, now I understand. Thanks.
No worries.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks