fantasy football spreadsheet

R

rorybreaker66

New Member
Joined
Aug 20, 2011
Messages
5
Hello everyone, (hopefully) quick question...
I'm setting up a spreadsheet for our fantasy football auction league and i've hit a brick wall.
i have a column where i would like to calculate the average dollar remaining per roster spot. I was thinking of b22/countblank(b4:b19)
where b22 is the total remaining and b4:b19 is the set of values to check for blank spots
but i can't get it to work right. any help would be greatly appreciated!
thanks!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
rorybreaker66 said:
Hello everyone, (hopefully) quick question...
I'm setting up a spreadsheet for our fantasy football auction league and i've hit a brick wall.
i have a column where i would like to calculate the average dollar remaining per roster spot. I was thinking of b22/countblank(b4:b19)
where b22 is the total remaining and b4:b19 is the set of values to check for blank spots
but i can't get it to work right. any help would be greatly appreciated!
thanks!
Click to expand...
Based on the limited info, that sounds like it should be the correct formula to use.

What's not working right?
 
Upvote 0
<table border="0" cellpadding="0" cellspacing="0" width="313"><col style="width: 139pt;" width="185"> <col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 139pt;" height="20" width="185">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td align="right">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20"><b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">name</td><td style=";">position</td><td style=";">$</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style=";">qb 1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Ray Rice</td><td style=";">rb1</td><td style="text-align: right;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Felix Jones</td><td style=";">rb2</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Calvin Johnson</td><td style=";">wr1</td><td style="text-align: right;;">23</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Vincent Jackson</td><td style=";">wr2</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style=";">flex</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style=";">te</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style=";">k</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style=";">def</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style=";">bench 1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style=";">bench 2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style=";">bench 3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style=";">bench 4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style=";">bench 5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style=";">bench 6</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style=";">bench 7</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">starting money</td><td style="text-align: right;;">189</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">total money spent</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">total remaining</td><td style="text-align: right;;">139</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">average remaining</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B21</th><td style="text-align:left">=+SUM(<font color="Blue">C4:C19</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B22</th><td style="text-align:left">=SUM(<font color="Blue">B20-B21</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B23</th><td style="text-align:left">=C22/COUNTBLANK(<font color="Blue">C4:C19</font>)</td></tr></tbody></table></td></tr></table><br />
</td> <td>
</td> <td align="right">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td align="right">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td align="right">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td align="right">
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td align="right">
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td align="right">
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td align="right">
</td> <td>
</td> </tr> </tbody></table>
 
Upvote 0
rorybreaker66 said:
<TABLE cellSpacing=0 cellPadding=0 width=313 border=0><COLGROUP><COL style="WIDTH: 139pt" width=185><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 139pt; HEIGHT: 15pt" width=185 height=20>

</TD><TD style="WIDTH: 48pt" width=64>

</TD><TD style="WIDTH: 48pt" width=64>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD>

</TD><TD align=right>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>name</TD><TD>position</TD><TD>$</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right"></TD><TD>qb 1</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>Ray Rice</TD><TD>rb1</TD><TD style="TEXT-ALIGN: right">13</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD>Felix Jones</TD><TD>rb2</TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD>Calvin Johnson</TD><TD>wr1</TD><TD style="TEXT-ALIGN: right">23</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD>Vincent Jackson</TD><TD>wr2</TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right"></TD><TD>flex</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right"></TD><TD>te</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right"></TD><TD>k</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right"></TD><TD>def</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right"></TD><TD>bench 1</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right"></TD><TD>bench 2</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right"></TD><TD>bench 3</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right"></TD><TD>bench 4</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right"></TD><TD>bench 5</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right"></TD><TD>bench 6</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right"></TD><TD>bench 7</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">20</TD><TD>starting money</TD><TD style="TEXT-ALIGN: right">189</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">21</TD><TD>total money spent</TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">22</TD><TD>total remaining</TD><TD style="TEXT-ALIGN: right">139</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">23</TD><TD>average remaining</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>
Sheet1​


<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B21</TH><TD style="TEXT-ALIGN: left">=+SUM(C4:C19)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B22</TH><TD style="TEXT-ALIGN: left">=SUM(B20-B21)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B23</TH><TD style="TEXT-ALIGN: left">=C22/COUNTBLANK(C4:C19)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


</TD><TD>

</TD><TD align=right>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD>

</TD><TD align=right>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD>

</TD><TD align=right>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD align=right>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD align=right>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD align=right>

</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD align=right>

</TD><TD>

</TD></TR></TBODY></TABLE>
Click to expand...
Shouldn't the average formula be:

=B22/COUNTBLANK(C4:C19)

Using that formula on the posted sample data I get a result of 11.5833333333333.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Sharing part of workbook
2
Replies
10
Views
917
Candyman8019
C
T
  • Question Question
Razz spreadsheet for sports cards
Replies
3
Views
283
Eric W
Eric W
G
  • Question Question
Embedded Range or Array Lookup
Replies
5
Views
484
Fluff
Fluff
T
  • Question Question
Formula to sum multiple sequences/ranges of values within an array?
Replies
2
Views
304
Fluff
Fluff
F
  • Question Question
Criteria in Countifs to exclude cells that contain any words in a list
Replies
2
Views
2K
Fluff
Fluff

Forum statistics

Threads
1,224,570
Messages
6,179,610
Members
452,931
Latest member
The Monk

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top