Excel 2010: Mode.Sngl not returning lowest of multiple modes

**yukontornado** · 10-17-2013, 04:05 PM

Hi everyone -
I appreciate some insight on the formula mode.sngl: I am using this to look at some columns of numbers and I have found that lets say I have 10 columns of numbers and there are two repeating sets of numbers (2's) and (5's). Each one of these repeats three times - the way the formula reads it should return a 2 as the mode. What mine is doing is what ever number appears first in the list of numbers is the one excel is picking for the mode....example: 1,1,5,5,5,2,2,2,3,7 mode = 5 when it is supposed to return the lowest mode of multiple modes which should be 2 in this case. If I change the order of the numbers and put a 2 in the list ahead of the 5's and both repeating three times then the mode chooses 2 as the mode....What is going on here??
Thank you in advance -
Chris

**Ace_XL** · 10-17-2013, 04:09 PM

Excel reads data from left to right and top to bottom. As such, in case of multiple Modes Excel will always return the one first found. thsi is true for all MODE functions in Excel.

A workaround would be to sort numbers in ascending order to return the lowest mode

**yukontornado** · 10-17-2013, 04:26 PM

Thank you for the info Ace.....I find it interesting the formula rule states that it will return the lowest multiple mode value and that it actually will not based on the info you gave me!! LOL
So, I will try to add a sort to my formula before the mode.sngl and see what happens.....
Not sure the best way to do that as my values that I am trying to mode are part of an order of different time trials that I don't want to visually change their order in the list. suggestions?