Forum Discussion

Phoenux's avatar
Phoenux
Copper Contributor
Jun 11, 2022

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 ce...
excel
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jun 11, 2022

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.

 

  • Phoenux's avatar
    Phoenux
    Copper Contributor
    Jun 12, 2022

    Riny_van_Eekelen 

     

    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! 🙂

     

Resources