Forum Discussion

Copper Contributor

Aug 05, 2021

Solved

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

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

Formulas and Functions

HansVogelaar
Aug 05, 2021
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.

HansVogelaar

MVP

Aug 05, 2021

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.

rsmccle

Copper Contributor

Aug 05, 2021

HansVogelaar 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