Rank Teams in Performance Order - not as easy as just =Rank...

Hi guys,

I need a formula, or some formulas to work out the following. This is a bit of a mathematical question/problem :-)

I want to rank some teams in order of performance, but it is not as easy as using "=rank...".

There are 20 teams. Teams are audited and have '*' number of green outcomes and '*' number of red outcomes. However, they may have 1 audit or 100 audits and the number of audits for each team is not equal (some teams audited more than others). Also, although there may be 10 audits for a team, the red and green outcome may not equal 10, as there can be more than 1 red and/or green for each audit. So, it is not as simple as ranking just the number of outcomes.

So, as each Team may have more audits than another, they can obviously have more red and/or green outcomes, but it isn't fair to rank their performance compared to someone who has had much less number of audits. As they have more reds than the next team, that doesn't meant they have done worse as comparatively they have more audits.

I want to make the results fair, so that a Team with fewer audits compared with a Team with more audits is assessed equally. Is that possible??

Is there a way of doing this? I could use a helper column/multiple formulas if needed.

I think I need to make a constant based on the number of audits in total and the number for each team, but when I tried earlier, all I got was that the team with the highest number of greens was ranked first, even though comparatively, they had more audits so that was to be expected...

Example data would be:

Team Number	No. of Audits	Greens	Reds
Team1	4	3	5
Team2	20	22	18
Team3	14	8	25
Team4	15	8	12
Team5	40	56	16

Thank you in anticipation of any replies :-)
ExcelNat

How about greens/total evaluations. Question: are greens and reds the only possible outcome? if so,

Please Login or Register  to view this content.

Thank you for your reply.

Yes, there are only green or red outcomes.

Yes, I think your calculation is a step closer...

However, a Team with only 1 audit, that ends up with 1 green and 0 reds (total only 1 outcomes) then has 100% green and is ranked as number 1.

Do you think this is fair, or do you think that I should only take into account a minimum number of audits to make it fair? Say, a minimum of 10 audits otherwise their figures aren't taken into account? Or is there another way to do it?

Thanks again,
ExcelNat