Rank formula needed

[tjdate]

Evening again guys,

Unfortunetely, this is my second question in two days as my latest project has me visiting some formulas that I'm not particularly familiar with.

I need to rank a team based upon its score in one column and if this is the same score as another team, then the deciding factor is then what each team scored in another column. I am aware that there are a few ways to do this, however, I also need the same formula to ignore blank cells so that a team that did not enter does not receive any points.

Hopefully the attached spreadsheet shows what I mean.

Thanks again for the help.

TJ

[daddylonglegs]

Try this formula in I2 copied down

=IF(H2=0,"",RANK(H2,H$2:H$7)+COUNTIFS(H$2:H$7,H2,E$2:E$7,">"&E2))

[tjdate]

Fantastic! Solves the problem fantastically!

Have to same I'm loving the help on this forum!

Thanks

TJ

[tjdate]

Morning all,

I have just been doing final testing and realised that although the above formula works perfectly, it inadvertently means that another formula doesn't work as when two scores are exactly the same in both columns meaning that they are both ranked as 1st (which is fine), the next team is ranked as 3rd and not second.

When two teams score the same number of points there position is then decided on the number of goals that they score which the above formula does perfectly, i still need this to work, however, if two teams score the same number of points AND score the same number of goals, they would rightfully both be awarded 1st place which is what i would want, however, the next highest scoring team should then be placed as 2nd and not as 3rd

I hope that makes sense.


TJ

[daddylonglegs]

That complicates the calculation a little but you can use this "array formula" in I2

=IF(H2=0,"",SUM(IF(FREQUENCY(IF(H$2:H$7*1000+E$2:E$7>H2*1000+E2,H$2:H$7*1000+E$2:E$7),H$2:H$7*1000+E$2:E$7),1))+1)

confirm with CTRL+SHIFT+ENTER