Results 1 to 2 of 2

Finding MODE w/ text in cells

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 ...

  1. #1
    Board Regular
    Join Date
    Jul 2010
    Posts
    290

    Default Finding MODE w/ text in cells

    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

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,591

    Default Re: Finding MODE w/ text in cells

    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...

    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
    
    2) Then try the following worksheet formula...

    =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)
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com