Forum Discussion

chancelin's avatar
chancelin
Copper Contributor
Dec 24, 2018

The mode of a series of numeric values

Hello, I am looking for an excel function that will allow me to determine the mode of a series of nominal values. The MODE () function is used but only for numeric values. From a PivotTable or frequ...
Formulas and Functions
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Dec 25, 2018
Sorry, but I did not understand the question well!
chancelin's avatar
chancelin
Copper Contributor
Dec 25, 2018

In fact your formula allows to find the mode of a nominal series. Once I've determined the mode, I find a formula to count the number of times that mode is in the nominal series

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Dec 25, 2018

    Okay,

    Simply, you use COUNTIF function to do so.

    =COUNTIF(A2:A8,C3)

    Result:

    3

     

    Or you can modify the original formula as below to display the nominal mode and the number of its occurrences all in a single cell.

    =INDEX($A$2:$A$8,MATCH(MAX(INDEX(COUNTIF(A2:A8,A2:A8),)),INDEX(COUNTIF(A2:A8,A2:A8),),0))&" / "&COUNTIF(A2:A8,INDEX($A$2:$A$8,MATCH(MAX(INDEX(COUNTIF(A2:A8,A2:A8),)),INDEX(COUNTIF(A2:A8,A2:A8),),0)))&" Times."

    Result:

    cat / 3 Times.

     

     

     

    Hope that helps

Resources