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 frequency distribution I can determine the mode.
My problem is that I want to do it only from an Excel function.
Thank you

Formulas and Functions

5 Replies

Sort By
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Dec 24, 2018

    Hi,

     

    You can provide each nominal value with a numeric ID, and depend on this ID in MODE function to determine the ID of the most occurred nominal value.

    After you got the ID, you can use it in VLOOKUP to return the nominal value.

     

     

    Instead, you can depend on this formula:

    =INDEX($A$2:$A$8,MATCH(MAX(INDEX(COUNTIF(A2:A8,A2:A8),)),INDEX(COUNTIF(A2:A8,A2:A8),),0))

     

     

    Hope that helps

    • chancelin's avatar
      chancelin
      Copper Contributor
      Dec 24, 2018

      Good evening Haytham and especially thank you for your message your method function very well. Once the known mode, if I know the number of instances there is this mode, is there also a formula?

Resources