Calculate average

[Winehouse]

Hi.
I co-run a squash league & I want to use the finishing positions to create seedings for a cup completion over the previous 3 leagues.

I give everyone a number where they finished in the leagues, then divide this by 3.

My formula so far is this

SUM(B2:D2)÷3

Due to injuries etc, not everyone plays in all leagues. So some players will have a blank in their cell. This gives a rogue result as my formula counts the blank as zero.

How can I create the average which ignores any blank cells?

Thank you.

 

[sheetspread]

Use the average function instead, it excludes blanks.

 

[Winehouse]

Thank you!

 

[Winehouse]

Just another quick one I’ve thought of.

If the average of the league positions are the same, i’d like to separate the players as follows.

The player having the highest position most recently, having the higher seed.

Can’t think how to do this.

 

[offthelip]

The easy way to do this is: assuming that you have 100 position in the squash league, multiply the value you get from your average as calculated above by 100, then add the position from the latest league.
For example if two people have results , 2nd and 4th but in differnet order, giving an average of 3, multipy this by 100 to get 300 add in the latest score one gtes 302 and the other gets 304

 

[sheetspread]

Look here also:

https://www.mrexcel.com/forum/excel-questions/410456-rank-w-multiple-tie-breakers.html