FREQUENCY HELP PLEASE

[JayNich]

Hey my name is Jay, I am currently trying to average the number of times each
number 1-47 occurs in cells A1-E74

e.g.
1 1 1 1
2 2 2 2
3 3 3 3
4 6 5 7
2 1 4 6

Frequency of the number 1 = 5

I have tried the frequency option but it includes ALL the numbers one, even
the 1's in the double digits.

e.g.
1 2 4 5
21 3 41 5

It says 1 = 3

I have tried adding a * to the number. Still nothing.

e.g.
=FREQUENCY(A1:E74,1*)

 

[Mark Ivey]

Why not try a pivot table...

 

[T. Valko]

Try this array formula** :

Table = A1:E74

=AVERAGE(COUNTIF(Table,ROW(A1:A47)))

Or, if you might ever insert new rows above the table range:

=AVERAGE(COUNTIF(Table,ROW(INDIRECT("1:47"))))

Based on your sample data and using the numbers 1 - 7 each number appears an
*average* of 2.8 times.

 

[T. Valko]

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)