Trying to figure out a MAX(IF) and MAX(IF) and a STDEV(IF)

Hi,

As per usual, I'm experimenting with Sports data and am just trying to figure out the relative strengths of some of the European Football leagues (and I'll make it clear from the top I'm not comfortable with the numbers I have at the moment which make Marseille the best team in Europe - but I'm happy with the principles of the model if that makes sense).

Basically I have attached the output from one of the models. In short I have managed to a couple of AVERAGEIF's to pull up the averages of the 5 leagues that I have put in this model (F1 = France, D1= Germany, SP1 = Spain, I1 = Italy, E0 = England). These have shown that relative to the other leagues, the French league appears to have the higher ranking teams relatively (), and almost the best home advantage (can't figure why France and Spain's home advantage is about 0.2 higher on average than England, Germany and Italy but am happy with the concept of the model).

In short I also would like to pull up the Max and Min values by league and which teams are involved (just to get an idea of things relatively). I have put the Germany line in manually (from N10 right on the first worksheet), but was just wondering if there was a way I could automate this effectively with a function (or several if there is a workaround)?

I also have a separate issue on the second worksheet (Scores), where I would just like to try and isolate the Standard Deviation of some error figures that have come out of the model (by league). The Standard Deviation for each of the 5 leagues error in the model is about 1.64, I'm thinking some leagues error will be lower (around the 1.5 mark) and just wondered if there was a way that I could pull this up?

I'd be grateful, if anyone could offer me some advice or nudges in the right direction.

Thanks in advance,

Euro Workup.xlsx

Just a quick update on this - as I've now had another chance to look at this; I've used an INDEX and a MATCH to pull the right teams up against the ratings that I pulled up using the array formulas above.

E.g =INDEX($D$10:$D$107,MATCH(O10,E$10:E$107,0))

The column references are slightly different as I pasted it into a different worksheet, with Column D being the teams and Column E being the ratings, with O10 in this case being the rating pulled up by the array formula (to get the MATCH to reference to). Basically I have highlighted these yellow in the attached.

In short is there a more elegant or better way of doing this? One small issue I can see is if two teams were to have the same ratings. Is there a way I can get the INDEX or MATCH to reference column C or J as a fallback to essentially act as a catch if two teams from different leagues had the same rating?

Thanks for any suggestions,

Euro_Idea_Output_110514.xlsx