I have multiple rows, of 18 columns, containing individual player game scores. Blank cells represent missed games. End of season ranking are determined by those players with the highest average game scores over 18 rounds dropping the three lowest scores including missed games. I am currently using a table with a column of end of season game scores then manually subtracting the three lowest scores (blanks count as zero) from the total and dividing by 15. Having a fx or combination of to auto calculate this final step would be much appreciated.

Blank cells represent missed games. End of season ranking are determined by those players with the highest average game scores over 18 rounds dropping the three lowest scores including missed games.The simplest solution is to change your Blank Cells to Zero and the first formula will work.

=(SUM(A1:A18)-SMALL(A1:A18,1)-SMALL(A1:A18,2)-SMALL(A1:A18,3))/(COUNT(A1:A18)-3)

The SMALL() function and the COUNT() function both ignore Blanks,

but Excel recognizes Zero as a valid number.

MIKE

Instead of manually changing your blank cells to zero, you could use a Helper Column.

A helper column is a "intermediate" column between the actual data and the final result.For example, with your real values, including blank cells in Columns A, drag this formula down Column B (or any other column, which you could then hide)

=IF(A1="", 0, A1)

Now use Mike's formula against the Helper Column, since there are no blank cells in that column.

=(SUM(B1:B18)-SMALL(B1:B18,1)-SMALL(B1:B18,2)-SMALL(B1:B18,3))/(COUNT(B1:B18)-3)

Note: If I understand your requirements, you are always dividing by 15, so the COUNT function is probably not needed.

=(SUM(B1:B18)-SMALL(B1:B18,1)-SMALL(B1:B18,2)-SMALL(B1:B18,3))/15

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Thank you Mike and DerbyDad03. I will give it a go this weekend and report back. The Helper Column will make a difference (I think) as I use the COUNT function for other purposes. There are about 80 to 90 players playing twice a week in two competitions, in 9 divisions of 12 and 16 of teams of 3 and four etc so keeping tabs on numbers via Count fx is important. Thank you both

Geoff

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History