Forum Discussion
I have a hard time excluding a value from my mode.simple formula...
Hello everyone!
Complete beginner with excel here and I would like to get help figuring out how to exclude a certain value from my mode.simple formula.
Explanations
So let's say I have cells ranging from a1 to c10
So for the purpose of an exercise I have found the most frequent value using the mode.simple formula using =mode.simple(a1:c10)
Let's say the most common value was 4.
Now that I have the result, I want to find the second most frequent value and here is the problem.
I tried using the =mode.simple(if(a1:c10 <>4,a1:c10)) , but excel won't let me proceed with the formula, giving me a pop-up window saying there is an error with my formula and asking if I'm trying to do a formula or not.
I tried creating an array formula using ctrl+shift+enter instead, but it gives me the same error...
Any advices please?
Thank you in advance! 🙂
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.
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! 🙂
