Still Another Excel Problem

[britrock88]

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?)

[Bobble]

Originally Posted by britrock88

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?

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)

Originally Posted by britrock88

(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?)

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

[dolfin]

Originally Posted by Bobble

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.

[britrock88]

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

[Bobble]

Originally Posted by britrock88

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.

[henry296]

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.