Solved Calculate the Average of row of 18 numbers

November 10, 2015 at 01:06:50
Specs: Windows 7
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.


See More: Calculate the Average of row of 18 numbers

Report •

#1
November 10, 2015 at 08:19:19
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

http://www.skeptic.com/


Report •

#2
November 10, 2015 at 11:00:29
✔ Best Answer
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


Report •

#3
November 11, 2015 at 09:35:23
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


Report •
Related Solutions


Ask Question