How to get the second lowest value excluding zero and ignoring the duplicates in excel

microsoft-excel worksheet-function

Solution 1:

Here is a solution using support cells, this way it's easier to see the logic, however you can of course combine everything into one formula.

enter image description here

  • =COUNTIF(A1:A7,0) - count number of cells containing 0
  • =COUNTIF(A1:A7,SMALL(A1:A7,D1+1)) - count the number of cells containing the smallest value (excluding 0)
  • =SMALL(A1:A7,D1+D2+1) - the second smallest value

The big formula would look like this:
=SMALL(A1:A7,COUNTIF(A1:A7,0)+COUNTIF(A1:A7,SMALL(A1:A7,COUNTIF(A1:A7,0)+1))+1)

Related

My computer won't go into standby or hibernate
VLC fullscreen - dual screen
How does MySQL Auto Increment work?
Show all File Types in Windows Media Player (WMP)
Users removed from Local Administrators Group after reboot?
How can I easily mount a Windows share from my Mac?
Returning Count of Values of Column D if Column G has a blank Value
Error message when trying to retrieve a value from a range using VLOOKUP
How to remove keyrings in Ubuntu?
How do I recover the MBR on a Thinkpad t420?
Why String class has copy constructor? [duplicate]
Turning on computer using communication port