Ladder based on the last 4 weeks results

[andrew75]

Hi everyone,

It's been a while since I was last on this form, the last time someone helped me pretty quickly so I thought that I'd give it another try.

What am I trying to do? I run a Fantasy Football competition I using Excel to collate all of the stats and results. I'm wanting to create a ladder based on the last 4 weeks of results. Below is a table that I've created based on the results of some teams:

We have a 22 Game season and as you can see from the results some are XXX.X or XX.X in size. Other than looking at the right and then counting back I can't think of how I can do this.

I'm not after a graph, more a table that will display who's been the form scoring team over the 4 weeks.

I'm using Excel 2010.

Thanks for any assistance .. this has been doing my head in most of the day.

Regards ... Andrew

 

[ljungren13]

<table x:str="" style="border-collapse: collapse; width: 577pt;" border="0" cellpadding="0" cellspacing="0" width="763"><col style="width: 58pt;" width="77"> <col style="width: 21pt;" width="28"> <col style="width: 20pt;" width="27" span="8"> <col style="width: 26pt;" width="34" span="13"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 58pt;" width="77" height="17">Team Name</td> <td style="width: 21pt;" width="28">W1</td> <td style="width: 20pt;" width="27">W2</td> <td style="width: 20pt;" width="27">W3</td> <td style="width: 20pt;" width="27">W4</td> <td style="width: 20pt;" width="27">W5</td> <td style="width: 20pt;" width="27">W6</td> <td style="width: 20pt;" width="27">W7</td> <td style="width: 20pt;" width="27">W8</td> <td style="width: 20pt;" width="27">W9</td> <td style="width: 26pt;" width="34">W10</td> <td style="width: 26pt;" width="34">W11</td> <td style="width: 26pt;" width="34">W12</td> <td style="width: 26pt;" width="34">W13</td> <td style="width: 26pt;" width="34">W14</td> <td style="width: 26pt;" width="34">W15</td> <td style="width: 26pt;" width="34">W16</td> <td style="width: 26pt;" width="34">W17</td> <td style="width: 26pt;" width="34">W18</td> <td style="width: 26pt;" width="34">W19</td> <td style="width: 26pt;" width="34">W20</td> <td style="width: 26pt;" width="34">W21</td> <td style="width: 26pt;" width="34">W22</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">1</td> <td x:num="" align="right">23</td> <td x:num="" align="right">45</td> <td x:num="" align="right">54</td> <td x:num="" align="right">34</td> <td x:num="" align="right">23</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">2</td> <td x:num="" align="right">34</td> <td x:num="" align="right">24</td> <td x:num="" align="right">32</td> <td x:num="" align="right">22</td> <td x:num="" align="right">54</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>
<table x:str="" style="border-collapse: collapse; width: 348pt;" border="0" cellpadding="0" cellspacing="0" width="464"><col style="width: 48pt;" width="64" span="2"> <col style="width: 45pt;" width="60"> <col style="width: 48pt;" width="64" span="3"> <col style="width: 63pt;" width="84"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" width="64" height="17"><!--[if gte vml 1]><v:shapetype id="_x0000_t201" coordsize="21600,21600" o:spt="201" path="m,l,21600r21600,l21600,xe"> <v:stroke joinstyle="miter"/> <vath shadowok="f" o:extrusionok="f" strokeok="f" fillok="f" o:connecttype="rect"/> <o:lock v:ext="edit" shapetype="t"/> </v:shapetype><v:shape id="_x0000_s2049" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:48pt;height:12.75pt;z-index:1;visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s2050" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:48pt;height:12.75pt;z-index:2;visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s2051" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:48pt;height:12.75pt;z-index:3;visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s2052" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:48pt;height:12.75pt;z-index:4;visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>0</x:Max> <x:Inc>1</x:Inc> <x:Page>8</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>20</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s2053" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:48pt;height:12.75pt;z-index:5;visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s2054" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:48pt;height:12.75pt;z-index:6;visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>0</x:Max> <x:Inc>1</x:Inc> <x:Page>8</x:Page> <x:Dx>16</x:Dx> <x:Sel>5</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>20</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><![endif]--> </td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64"> </td> <td class="xl24" style="border-left: medium none; width: 45pt;" width="60">Week #:</td> <td class="xl24" style="border-left: medium none; width: 48pt;" x:num="" align="right" width="64">5</td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64"> </td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64"> </td> <td class="xl24" style="border-left: medium none; width: 63pt;" width="84"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">Rank</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">Team</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">W2</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">W3</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">W4</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">W5</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">4 Week Total</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">1st</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">2</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">34</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">32</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">22</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">54</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=SUM(C3:F3)" align="right">142</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">2nd</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">23</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">54</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">34</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">23</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=SUM(C4:F4)" align="right">134</td> </tr> </tbody></table>

based on that data, are you looking for something to do this?

 

[andrew75]

Thanks for the quick reply, yeah that's basically what I'm after. I think it's the week variable that is throwing me out.

 

[ljungren13]

the formulas i made are kinda long and wont look very good on here but ill find a place to upload this and show you

 

[andrew75]

Great!! Thanks for that

 

[ljungren13]

http://spreadsheets.google.com/ccc?key=tRf7_jidZ4I08ya8cyT-Y3w#gid=0

sheet 2 is where the 4 weeks are summarized

of course you can sort that data to show who did the best and worst and what not

 

[andrew75]

Thanks ... I've just requested access to the document

 

[ljungren13]

sorry never used google docs, reimported it with public status see if this works:

http://spreadsheets.google.com/ccc?key=tg8GYQIe7me9uKPVstH5ZbQ#gid=0