Roto Points/Excel help

[Nat Uralice]

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!

[Matthias]

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.

[Nat Uralice]

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.

[wrveres]

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