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]

Appears MODE doesn't work with non-contiguous named ranges.

You can write a custom function as a workaround.

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

Cell Formulas
Range	Formula
E2	E2	=MODE(data)
E3	E3	=MODE(A2:A8,C2:C8)
E4	E4	=myMode(data)

Named Ranges
Name	Refers To	Cells
data	=Sheet1!$A$2:$A$8,Sheet1!$C$2:$C$8	E2:E4

Option Explicit

Function myMode(data As Range) As Double
    Dim i As Long, c
    For Each c In data
        i = i + 1
    Next
    ReDim a(1 To i, 1 To 1)
    i = 0
    For Each c In data
        i = i + 1
        a(i, 1) = c
    Next
    myMode = Application.Mode(a)
End Function

 

[JGordon11]

shorter, more efficient version:

Function myMode(data As Range) As Double
    Dim i As Long, c
    ReDim a(1 To data.Count, 1 To 1)
    For Each c In data
        i = i + 1
        a(i, 1) = c
    Next
    myMode = Application.Mode(a)
End Function

 

[kweaver]

Appears MODE doesn't work with non-contiguous named ranges.

You can write a custom function as a workaround.

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

Cell Formulas
Range	Formula
E2	E2	=MODE(data)
E3	E3	=MODE(A2:A8,C2:C8)
E4	E4	=myMode(data)

Named Ranges
Name	Refers To	Cells
data	=Sheet1!$A$2:$A$8,Sheet1!$C$2:$C$8	E2:E4

Option Explicit

Function myMode(data As Range) As Double
    Dim i As Long, c
    For Each c In data
        i = i + 1
    Next
    ReDim a(1 To i, 1 To 1)
    i = 0
    For Each c In data
        i = i + 1
        a(i, 1) = c
    Next
    myMode = Application.Mode(a)
End Function

Thanks...Apparently NOT...as I discovered and pointed out in my post. In this particular situation, I can't use a macro or UDF, but appreciate your suggestion.

 

[Eric W]

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.

 

[Marcelo Branco]

I think this should work too

=MODE(Data:Data)

M.

 

[kweaver]

Marcelo: fantastic! I didn't think to try that. Perfect!!

 

[Marcelo Branco]

Marcelo: fantastic! I didn't think to try that. Perfect!!

kweaver,

I did some tests and noticed that this formula can have problems. It should only be used when there are no values between non-contiguous ranges. For example if Data contains the ranges A2:A7 and C2:C7 the formula will count values in B2:B7 as well, because the union Data:Data results in the range A2:C7...

Therefore, this formula, although elegant, is not recommended.

M.

 

[kweaver]

When I realized that a named range didn't work, I use this:

=MODE(M12:N12,C13:N16,C17:J17)

but your example works perfectly with the named range referencing that block.

 

[Marcelo Branco]

I can see that in your case the named range Data is composed by 3 ranges: M12:N12; C13:N16 and C17:J17. The formula you used is perfect, as it only counts values in the 3 ranges.

What I tried to say is that using the formula =MODE(Data:Data) values in C12:N12 and K17:N17, which don't actually belong to the named range Data, will be counted as well. This is because the union Data:Data results in C12:N17.

So be careful when using a formula that uses the union Data:Data as the argument of any function.

M.