MODE.SNGL calculates a value of 0 when few values of 187 points are 0 (only 3)

[rsmccle]

I have a vertical array of 187 values that vary from 0-100. Only three of the values are 0. The average and the median are  17 an 18, respectively. Why does the MODE.SNGL calculate 0? Seems it should be closer to the average and median values?

[#2 · Accepted solution]

@rsmccle 

MODE.SNGL returns the value that occurs most frequently. If there is more than one such value, it returns the first one it encounters.

So if there are no values in your array that occur 4 or more times, it is quite possible that MODE.SNGL returns 0.

[#3]

@Hans Vogelaar Brilliant, Hans! And thanks for the quick reply!

I hadn't considered the thought that it was literally looking for EXACT values. There were 3 values that were exactly 0, while there were probably no other values that occurred even twice, i.e., the other values were of the order 19.91814, ranging from 0 to 31. So I after I used the ROUND function on the data, creating whole numbers for the data (like 17) it returned a MODE of 19 for the data. This looks completely valid, as the Mean and Median of the data were 16.847 and 17.75, respectively.

Again, thanks for the quick help!

Best regards,

Scott