The mode of a series of numeric values


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



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.

Get the Mode in nominal values.png



Instead, you can depend on this formula:


Get the Mode in nominal values 2.png



Hope that helps

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?

Sorry, but I did not understand the question well!

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



Simply, you use COUNTIF function to do so.





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


cat / 3 Times.


Get the Mode in nominal values.png



Hope that helps

Related Conversations
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies
Pivot table
gabriellerocha in Excel on
5 Replies
PWA fullscreen like IE11 kiosk mode
rogihee in Discussions on
5 Replies
working on a series
MBARRY95 in Excel on
2 Replies