MODE not working with named range

[kweaver]

I've created a named range for a block of data. AVERAGE, MEDIAN, COUNT, COUNTIF, and STDEV all work with the named range.
MODE, however, returns a #VALUE error. If I use MODE without the named range by dragging over the data (need to use CTRL as I drag because the data is not contiguous), it works.
There is only one MODE for the data.

 

[JGordon11]

You can trick Excel into doing it:

Book1
	A	B	C	D	E
1
2	1		1		#VALUE!
3	2		2		2
4	3		2		2
5	4		3		2
6	5		6
7	6		7
8
Sheet1

Cell Formulas
Range	Formula
E2	E2	=MODE(data)
E3	E3	=MODE(A2:A7,C2:C7)
E4	E4	=MODE(INDEX(data,,,1),INDEX(data,,,2))
E5	E5	=MODE(SMALL(data,ROW(INDIRECT("1:"&COUNT(data)))))

If you know how many different areas are in the range, then try the E4 formula. If not, try the E5 formula.

Eric W's modified E4 formula can be used with a bounding number of areas (as a CSE formula pre 365) , e.g.,

=MODE(INDEX(data,,,ROW(1:10))) where 10 is here is used to bound the max number of areas in the range named data.