Roto Points/Excel help - Fantasy Baseball Cafe 2014 Fantasy Baseball Cafe
100% Deposit Bonus for Cafe Members!

Return to Baseball Leftovers

Roto Points/Excel help

Moderator: Baseball Moderators

Roto Points/Excel help

Postby Nat Uralice » Sun Feb 27, 2011 11:24 am

Hello all!

I know there are many Excel wizards on this site based on all the invaluable information I've come across over the years, so I'm reaching out with a somewhat odd request.

I run a H2H league, and we decided last year to award weekly incentives to whoever had the best week. However, as everyone knows, teams can blow their opponents out of the water in H2H leagues with a subpar showing if they're simply playing a team with an even worse showing. As a result, we figured the best way to determine who had the "best" week (and therefore deserved to win the weekly stipend) was to use rotisserie scoring for the week. Whoever had the best roto score for a that week would win the money. Last year, I simply went through and calculated everything manually. Not a huge pain, but I would think there has to be an easier way.

As a result, I'm asking if anyone can help me out with some sort of Excel roto matrix, where I can just enter the weekly totals for everyone and it will calculate their roto score accordingly. Any help at all would be greatly appreciated!
Can't worry about tomorrow's pain tonight.
Nat Uralice
College Coach
College Coach

User avatar

Posts: 237
Joined: 12 Jan 2005
Home Cafe: Baseball
Location: running from security to keep my beer...

Re: Roto Points/Excel help

Postby Matthias » Sun Feb 27, 2011 11:51 am

You would just need to use the Excel RANK function. If every team has their RBIs in cells A1:A12, then you would do =rank(a1,$A$1:$A:$12) and that would be your roto points. You can specify if you want to rank it in ascending or descending order which would depend if you were talking about big = good (HRs, RBIs) or big = bad (ERA, WHIP). Then just leave the sheet and each week repaste in the totals.

You can then use the SUMIF function to total the team totals.
0-3 to 4-3. Worst choke in the history of baseball. Enough said.
Matthias
General Manager
General Manager


Posts: 4860
Joined: 16 Mar 2005
Home Cafe: Baseball

Re: Roto Points/Excel help

Postby Nat Uralice » Sun Feb 27, 2011 12:47 pm

Ah ok, this definitely seems like it will help me out. However, my inexperience with Excel formulas is coming into play. I figured out which formula will work best (RANK.AVG), but it asks me to fill in more fields to complete it (or else I get a "circular logic" message). It asks for a Number, Reference, and Order fields; I tried selecting one cell for Number, the column for Reference, and 0/descending for Order to no avail. Any suggestions? Thanks for your help with this.
Can't worry about tomorrow's pain tonight.
Nat Uralice
College Coach
College Coach

User avatar

Posts: 237
Joined: 12 Jan 2005
Home Cafe: Baseball
Location: running from security to keep my beer...

Re: Roto Points/Excel help

Postby wrveres » Sun Feb 27, 2011 1:48 pm

Nat Uralice wrote:Ah ok, this definitely seems like it will help me out. However, my inexperience with Excel formulas is coming into play. I figured out which formula will work best (RANK.AVG), but it asks me to fill in more fields to complete it (or else I get a "circular logic" message). It asks for a Number, Reference, and Order fields; I tried selecting one cell for Number, the column for Reference, and 0/descending for Order to no avail. Any suggestions? Thanks for your help with this.

you are ranking A, your score, against B everybody else score.

RowA
1 = your score
2
3
4
5
6
7
8
9

so your script should rank =rank(a1,$A$1:$A:$9,1) the one signifies if you are ranking top to bottom, or reverse. For reverse replace the 1 with a 0. thats used for ERA and whip
25                "Love the Padres"
Rafael

Dodgers FAIL|Mets FAIL|Canada FAIL
wrveres
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicFantasy ExpertCafe WriterEagle EyeInnovative MemberCafe Musketeer
Posts: 31781
(Past Year: 734)
Joined: 2 Mar 2003
Home Cafe: Baseball

Re: Roto Points/Excel help

Postby IveGotTheRuns » Tue May 13, 2014 3:30 am

So I hope this thread isn't too old to revive, but I found it on google while trying to figure out how to assign roto points based on stats. I use Libre Office but the formulas are about the same as in excel. The problem with using a straight ranking is that the points are not calculated correctly in the even of a tie. I noticed that the function was just returning the lowest rank possible (i.e. if two teams are tied for first place with 44 home runs they would each receive 9 points instead of 9.5 points). To fix this I just used a bunch of nested IF statements and added points based on how many teams were tied. This is the basic layout of my formulas for Runs:

Column C (rows 2 - 11): Each team's number of runs
Column X: =RANK(C2,C$2:C$11,1)
Column M: =IF(COUNTIF(X$2:X$11,X2)=1,X2,IF(COUNTIF(X$2:X$11,X2)=2,X2+0.5,IF(COUNTIF(X$2:X$11,X2)=3,X2+1,IF(COUNTIF(X$2:X$11,X2)=4,X2+1.5,IF(COUNTIF(X$2:X$11,X2)=5,X2+2,IF(COUNTIF(X$2:X$11,X2)=6,X2+2.5))))))

I only went up to a 6 way tie because the most I've ever seen is 4 and I doubt a 7 or more way tie would ever occur. If it did I would just nest another IF statement into the last one. Here are the points added in order of how many teams are tied at a particular rank:

0: 0
1: .5
2: 1
3: 1.5
4: 2
etc.

Unfortunately for me this was the easiest part of the spreadsheet to program, lol. Our league is a H2H each category league but we give payouts to the top 2 roto point scorers every month (or 4 matchup period), so we have to manually import the weekly totals from ESPN each week (adjusting for the fact that HBP and a couple other outcomes are counted in weekly OBP but not available on the site) then sum everything up every fourth week. Luckily I was able to bend Libre Office over my knee and spank the formulas into working. Unluckily ESPN (unlike yahoo, which otherwise stinks) doesn't have fantasy API available for me to just rip all the data and have things work themselves out :\. Sorry for rambling, hope this helps someone. Let me know if you have any questions or want a copy of the spreadsheet.
IveGotTheRuns
Little League Legend
Little League Legend


Posts: 6
(Past Year: 6)
Joined: 13 May 2014
Home Cafe: Baseball


Return to Baseball Leftovers

Who is online

Users browsing this forum: No registered users and 6 guests

cron
Forums Articles & Tips Sleepers Rankings Leagues


Today's Games
Saturday, Sep. 20
(All times are EST, weather icons show forecast for game time)

LA Dodgers at Chi Cubs
(1:05 pm)
Detroit at Kansas City
(1:05 pm)
Toronto at NY Yankees
(4:05 pm)
Philadelphia at Oakland
(4:05 pm)
Arizona at Colorado
(4:10 pm)
Boston at Baltimore
(7:05 pm)
Milwaukee at Pittsburgh
(7:05 pm)
Chi White Sox at Tampa Bay
(7:10 pm)
indoors
Cleveland at Minnesota
(7:10 pm)
Washington at Miami
(7:10 pm)
indoors
Seattle at Houston
(7:10 pm)
NY Mets at Atlanta
(7:10 pm)
Cincinnati at St. Louis
(7:15 pm)
San Francisco at San Diego
(8:40 pm)
Texas at LA Angels
(9:05 pm)

  • Fantasy Baseball
  • Article Submissions
  • Privacy Statement
  • Site Survey 
  • Contact