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:
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.