G Guest Apr 3, 2007 #1 I need to return the 2nd lowest value in a contiguous number array but I want to exclude zero values, how can I write this formula?
I need to return the 2nd lowest value in a contiguous number array but I want to exclude zero values, how can I write this formula?
P Peo Sjoblom Apr 3, 2007 #2 One way =SMALL(IF(A1:A10<>0,A1:A10),2) needs to be entered with ctrl + shift & enter
H Harlan Grove Apr 3, 2007 #3 Peo Sjoblom said: One way =SMALL(IF(A1:A10<>0,A1:A10),2) needs to be entered with ctrl + shift & enter Click to expand... Standard quibble: condition should be > 0. If negative and positive values are 'valid', zero values should be as well. Anyway, this could be done without array formulas using =SMALL(A1:A10,COUNTIF(A1:A10,"<=0")+2)
Peo Sjoblom said: One way =SMALL(IF(A1:A10<>0,A1:A10),2) needs to be entered with ctrl + shift & enter Click to expand... Standard quibble: condition should be > 0. If negative and positive values are 'valid', zero values should be as well. Anyway, this could be done without array formulas using =SMALL(A1:A10,COUNTIF(A1:A10,"<=0")+2)
