This is a discussion on Finding MODE w/ text in cells within the Excel Questions forums, part of the Question Forums category; I am trying to find the MODE, or most common letter in this case, within a range (G4:U80) based on ...
I am trying to find the MODE, or most common letter in this case, within a range (G4:U80) based on criteria in G5:U81.
right now this is my formula an array
it is returning #N/A
is there a better way to find what I am looking for without MODE? I thought MODE will only find a numerical value and when using text it needs to be something else.
The following custom function returns the mode for data that contains text and/or numerical values. However, when the data contains only numerical data, use the native function MODE instead. It's probably more efficient.
1) Place the code for the custom function in a regular module...
2) Then try the following worksheet formula...Code:Function MyMode(X As Variant) As Variant Dim Y As Variant Dim MyArray() As Variant If TypeOf X Is Range Then Set X = Intersect(ActiveSheet.UsedRange, X) For Each Y In X.Cells With WorksheetFunction If .IsNumber(Y) Or .IsText(Y) Then Cnt = Cnt + 1 ReDim Preserve MyArray(1 To Cnt) MyArray(Cnt) = Y End If End With Next Y ElseIf IsArray(X) Then For Each Y In X With WorksheetFunction If .IsNumber(Y) Or .IsText(Y) Then Cnt = Cnt + 1 ReDim Preserve MyArray(1 To Cnt) MyArray(Cnt) = Y End If End With Next Y End If With Application MyMode = .Index(MyArray, .Mode(.Match(MyArray, MyArray, 0))) End With End Function
...confirmed with CONTROL+SHIFT+ENTER. Adjust the ranges, accordingly. Also, here are other possible worksheet formulas...
Microsoft MVP - Excel
xl-central.com - "For Your Microsoft Excel Solutions"