What is the correct use of MODE()

[spcalan]

I have a column ( 100 rows ) and I need to know what number shows up the most times.

Is mode the correct formula?

I keep getting #NUM! when I use:

=mode(a:a)

 

[VoG]

Your formula returns the correct results for me using Excel 2007. Perhaps avoid using whole column references in an earlier version

=MODE(A1:A20)

 

[roentgen]

I think you need to be more specific with your range. =MODE(A1:A100) would work. Selecting the whole column, I assume, tries to rationalise all the blank cells to a number, hence the #NUM! error.

 

[stanleydgromjr]

roentgen,

This is one way:

Excel Workbook
	A	B	C	D
1	1		1	3
2	2		2	3
3	3		3	3
4	1		4	4
5	2		5	1
6	3		6	1
7	1
8	2
9	3
10	4
11	4
12	4
13	4
14	5
15	6
Sheet1

The formula in cell D1 (copied down to the last entry in column C - the unique numbers in column A):
=COUNTIF(A:A,C1)


Have a great day,
Stan

 

[mortgageman]

Your formula returns the correct results for me using Excel 2007. Perhaps avoid using whole column references in an earlier version

=MODE(A1:A20)

I was curious so I just checked as well - I have version 2003. I tried it with the whole column ref and also got a #NUM error. Quite frankly, whole column refs scare me - it seems dangerous and plain lazy to use them. I think it is worthwhile knowing what range you are dealing with.