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
{=MODE(IF(C2:C78="SMITH",IF(G2:U78=1,IF(G3:U79="A",IF(ISNUMBER(G4:U80),G4:U80)))))}
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.
many thanks
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
=MyMode(IF(C2:C78="SMITH",IF(G2:O78=1,IF(G3:O79="A",IF(ISNUMBER(G5:O81),G4:O80)))))
...confirmed with CONTROL+SHIFT+ENTER. Adjust the ranges, accordingly. Also, here are other possible worksheet formulas...
=MyMode(A2:G100)
=MyMode(A:D)
Like this thread? Share it with others