Still Another Excel Problem
 

Easy Bubble Solver - Google Sheets

How do I automate column F, which outputs an in-conference rank corresponding to the conference in column E and the overall rank in column K?

(While we're at it, are there any more sophisticated ways to do the calculation in column K that ranks teams based on their EBS in column J, with ties broken in favor of the better RPI in column I?)

There's prolly a more elegant way but you could just put this formula in O2 and copy down (as long as your data is always sorted by rank):

=COUNTIF($E$2:E2,E2)


I'd look here: http://www.cpearson.com/excel/Rank.aspx under Double Ranking and Tie Breaking. Should give you some ideas.

This is a great resource!

In Column P
=(J2/MAX(ABS($J$2:$J$352)))+(I2/(10^(MAX(LEN(J$2:J$352)+1))))

Hit CTRL+SHIFT+ENTER as this needs to be an Array formula.

In Column O
=RANK(P2,$P$2:$P$352,1)+COUNTIF($P$2:P2,P2)-1

You could put all of this data on another sheet and use an offset formula to give you your names in order since your order will be changing.

Bobble, that COUNTIF idea is a clever one. Thanks!

You're welcome. It's quick and dirty but it isn't robust. If your data isn't sorted properly, it fails. If you re-sort the sheet, you might need to put the formula in again. So, be careful that it's still doing what you want when you manipulate your sheet.

If you data is already sorted properly, I use the following if you are ranking over a group:

IIF(A2=A1,C2+1,1)

Column C has the rank value.