For example:
[Column A] 1,1,2,2,3,3,3 = The average is 2.3
[Column B] 2,2,3,3,4,4 = the average is 2 (each number appears 2 times each)
[Column C] 2,2,2,3,3,3,4,4,4 = the average is 3 (each number appears 3 times each)
Add a comment |
3 Answers
With array formula it will be simpler:
=COUNT(A$1:A$10)/SUM(IF(A$1:A$10<>"",1/(COUNTIF(A$1:A$10,A$1:A$10)))) Array formula after editing is confirmed by pressing ctrl + shift + enter
Comments
=(ROWS(A1:A9)-COUNTBLANK(A1:A9))/SUMPRODUCT(1/COUNTIF(OFFSET(A1,,,COUNTA(A1:A9));OFFSET(A1,,,COUNTA(A1:A9)))) The lenght of the different columns makes it a bit tricky though(hence using OFFSET and COUNTA).
Comments
Another option (with blank cells support):
=AVERAGE(COUNTIF(C5:C13,UNIQUE(C5:INDIRECT(ADDRESS(COUNTA(C5:C13)+ROW(C4),COLUMN(C4)))))) Output (French software, O365) :
