Resource 2017 AFL Draft Order

[DrMike]

That's a rabbit hole I probably won't go down, as it involves macros and things and would probably break googlesheets (if it doesn't break me first!) I can do a manual sort before I screenshot it each week though, I doubt it changes too much.

If you hadn't automated it, SORT does this really easily in Google Sheets
https://support.google.com/docs/answer/3093150

I wish it was that easy in Excel

 

[Lore]

Needed to put $'s against all the ranges?

Nah it was to do with the way the conditional formatting thing works, it doesn't like when a formula returns a value, has to return a logical 'TRUE'. But once I figured that out I had to find a way of getting a formula that returns 'true', but if formulas don't like to find a value in an array. Ended up with a couple of extra columns (they're hidden) that do a bit of the work, and then when that worked it was clearing all of the colour formatting and just making it white background and bold. So now it's actually 8 different conditional formatting rules with nested AND formulas to ensure that the colours and the grey text come out right >.< Vastly overcomplicated and maybe there's an easier way, but at least it works. hah

If you hadn't automated it, SORT does this really easily in Google Sheets
https://support.google.com/docs/answer/3093150

I wish it was that easy in Excel

Yeah the automation stuffs that up. In fact the formulas that I have don't even like the Data>sort function, the numbers sort but the formulas for the team names 'sort' and then get changed back to the alphabetical team listing. I could fix that but again the automation... oh well. I don't think they'll move around too much anyway so it's not difficult to update it, it's not like I have to cross reference or anything to put it in order so it doesn't take long to do manually.

 

[DrMike]

So now it's actually 8 different conditional formatting rules with nested AND formulas to ensure that the colours and the grey text come out right >.< Vastly overcomplicated and maybe there's an easier way, but at least it works. hah

If I get a chance I'll have a look for a solution

Yeah the automation stuffs that up. In fact the formulas that I have don't even like the Data>sort function, the numbers sort but the formulas for the team names 'sort' and then get changed back to the alphabetical team listing. I could fix that but again the automation... oh well. I don't think they'll move around too much anyway so it's not difficult to update it, it's not like I have to cross reference or anything to put it in order so it doesn't take long to do manually.

You need to SUMIF or I think SUMPRODUCT into a background table, then use SORT on that range to show on the front page

SUMPRODUCT is easy you just do something like SUMPRODUCT(($PickHolderRange=$Team)*($PointValueRange))

 

[BEaston]

AH-HA! DrMike Finally got the traded picks to automatically bold themselves, they should be perpetually accurate now!

Round 8 (reposted, with clubs ranked on the right hand side according to their draft points, and a few tweaks to formatting):

View attachment 371868

DAAYYYAAM that's sexy

 

[Lore]

Round 9:

 

[Lore]

Round 10:

 

[Lore]

Round 11:

 

[Demon in Hell]

Sorry to put the cat among the pigeons.

Greater Western Sydney's 1000-point penalty for the Lachie Whitfield affair last year means their current pick 18 (worth 985 points) is worthless and shifted to the end of the draft.

In effect Brisbane now holds pick 18 and all clubs move up one spot.

Don't know how you can work that scenario into your spreadsheet.

 

[Lore]

Sorry to put the cat among the pigeons.

Greater Western Sydney's 1000-point penalty for the Lachie Whitfield affair last year means their current pick 18 (worth 985 points) is worthless and shifted to the end of the draft.

In effect Brisbane now holds pick 18 and all clubs move up one spot.

Don't know how you can work that scenario into your spreadsheet.

Given that pick is worth 985 points, do we take the last 15 points off their next pick as well? (St Kilda's original round 2 pick).

edit: Have altered the spreadsheet to reflect the above, hopefully it's accurate. In the draft order page I have listed it as a forfeited pick, given we don't know where the end of the draft will be until trade and free agency is complete and we know how many senior list spots each club has still available. I doubt it makes much difference anyway, as the pick numbers will be the same in that scenario.

I've also assumed that the values of all the remaining picks stay the same, so Brisbane's pick 18 has the same value that it had when it was the 19th pick in the draft.

This is what it looks like with those changes in place:

 

[dlanod]

I've also assumed that the values of all the remaining picks stay the same, so Brisbane's pick 18 has the same value that it had when it was the 19th pick in the draft.

If I had to guess I'd expect the values to move up with draft picks. I don't think the AFL will stuff around with "this pick is forfeited so picks are now worth differently through the rest of the draft".

 

[Lore]

If I had to guess I'd expect the values to move up with draft picks. I don't think the AFL will stuff around with "this pick is forfeited so picks are now worth differently through the rest of the draft".

Well the 15 points that came off GWS' pick 25 would be more than earned back by moving up. Originally that was pick 26 pick and worth 729 points, -15=714 (which is what I have the value set to at the moment). If the pick value moves up to the value usually given to pick 25, then the pick becomes 756-15, which is 741. I don't know if that exactly makes sense. Either way it doesn't change the order of the picks, so it only makes a difference if people are actually looking closely at the point values in the draft order tab (so if they're doing a phantom draft with bidding, I guess).

 

[Demon in Hell]

Apologies for opening this can of worms. But I thought I read somewhere that GWS could take the last 15 points off their last pick.

I'll see if I can find the article.

 

[Lore]

Apologies for opening this can of worms. But I thought I read somewhere that GWS could take the last 15 points off their last pick.

I'll see if I can find the article.

Nah it's good, I want it to be as accurate and useful as possible so any feedback is great The draft order that the AFL website put out this morning says it comes off their next pick (there's a comment at the bottom explaining GWS's penalty).

 

[Demon in Hell]

"The Giants' first pick (no.18, worth 985 points) will shift to the end of the draft as a result of the 1000 point penalty placed on them due to the Lachie Whitfield affair. The remaining 15 points will be taken off the value of their next pick."

Comes off pick 25

 

[Lore]

"The Giants' first pick (no.18, worth 985 points) will shift to the end of the draft as a result of the 1000 point penalty placed on them due to the Lachie Whitfield affair. The remaining 15 points will be taken off the value of their next pick."

Comes off pick 25

Yep, that's what I've got, so it should be right now Only known potential issue now is whether or not those picks move up in value - i.e. does Brisbane's pick 18 have a value of 985, or the value of pick 19?

 

[borderbarry]

Perhaps it would make more sense if Brisbane retained pick 19, and all the picks after retained thier value. and there be no pick 18.

 

[Lore]

Perhaps it would make more sense if Brisbane retained pick 19, and all the picks after retained thier value. and there be no pick 18.

That's essentially what I did I just changed the pick numbers (so the values are the same as they were). Pick 18 is the start of round 2.

 

[Lore]

Round 12:

 

[JG22]

Pick 2 works

 

[harro92]

Round 12:
View attachment 381016

Why doesn't crows have a first round pick ?


Sent from my iPhone using Tapatalk

 

[Lore]

Why doesn't crows have a first round pick ?


Sent from my iPhone using Tapatalk

Haha, good pick up. My tinkering with the GWS pick has screwed it up.

 

[dlanod]

Why doesn't crows have a first round pick ?


Sent from my iPhone using Tapatalk

Adelaide gifted their first round pick to GWS out of the goodness of their hearts. That's just how they roll in SA.

 

[Lore]

Adelaide gifted their first round pick to GWS out of the goodness of their hearts. That's just how they roll in SA.

hahahahahahaha


Alright, harro92 (and everyone else) have a look and see what you reckon. I think I've fixed it and it shouldn't happen again. The 1000 points should come off regardless of where GWS are on the ladder and shouldn't come off from anyone else(!!) by accident. Even the colours seem to work properly DrMike

 

[DrMike]

Even the colours seem to work properly DrMike

Except the bold, everything is fine

 

[Lore]

Except the bold, everything is fine

Dagnamit.