• 504K Members
• 3,850 Online
• 601K Conversations

Contributor

# 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

5 Replies

# Re: The mode of a series of numeric values

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

# Re: The mode of a series of numeric values

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?

# Re: The mode of a series of numeric values

Sorry, but I did not understand the question well!
Highlighted

# Re: The mode of a series of numeric values

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

# Re: The mode of a series of numeric values

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies