Using excel, how do you calculate the average occurrence of the same numbers in a column?

Question

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)

Answer 1

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

Answer 2

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) :

Answer 3

=(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).