Forum Discussion
I have a hard time excluding a value from my mode.simple formula...
Phoenux Perhaps this will work for you.
=MODE.SNGL(IFERROR((1/(A1:C10<>MODE.SNGL(A1:C10))*A1:C10),"")) where the part,
1/(A1:C10<>MODE.SNGL(A1:C10))*A1:C10) "copies" the array replacing the most frequent number with #DIV0!
Then, IFERROR(-----------, "") replaces the errors with a blank. And finally, you use MODE.SNGL one more time to find the most frequent number in the array with the blanks, thus being the second most frequent number from the original array.
File attached.
- PhoenuxJun 12, 2022Copper Contributor
Hello!
Thanks for responding!
I ended up finding another method this morning to give me the results I wanted.
In case it can help others, here is what I used:
Mode.sngl(if(a1:c10<>4;a1:c10))
And for multiple exclusions:
Mode.sngl(if(a1:c10<>4;if(a1:c10<>7;a1:c10)))
Thanks for the help! 🙂