SMALL function to exclude zero values?

Originally Posted by dflak

You are going to have to use an array formula. . . .

There's a way to do this which doesn't require array formula entry, just longer, less efficient formulas.

=SMALL(INDEX(range+(range=0)*1E300,0),n)

will return the nth smallest value from range ignoring zeros without needing array formula entry.

FWIW, if Excel Online's spilled formula semantics would eventually become desktop Excel's spilled formula semantics, array formula entry may not be needed much longer. In my own testing, only IF, TRANSPOSE, MINVERSE and MMULT calls require array formula entry most of the time. Any expression involving only comparisons and arithmetic, such as the one above, can be put inside INDEX(...,0) or INDEX(...,0,0) to avoid array formula entry.

That said, using array formulas for this is more efficient, Excel performs fewer internal calculations to produce the result.

hrlngrv, The array formulas is not worse then others and problem not because of array and because of not correct usage.

Thank you so much, it worked! I appreciate everyone's helpful and timely responses. Thanks again!

Originally Posted by BMV

. . . The array formulas is not worse then others . . .

Yeah, I botched the X5 formula. Fixed now.

Thing is, your array formula (corrected to allow for negative values) =SMALL(IF(range<>0,range),1) is very efficient because the IF call replaces zero values with FALSE, and SMALL ignores those values. With N total items in range, and Z zero values, 2N - Z comparisons to get the result.

Thing is, if this formula were repeated in N - Z cells to produce the ranked (ascending sorted nonzero) values from range, the formula above would involve (N - Z)(2N - Z) comparisons because every formula would perform the same range<>0 comparisons. My 2-extra-cells approach performs 2N comparisons in X1 and X2 together to handle figuring out negatives and zeros. The X5 formula performs N + 1 comparisons, so N - Z such formulas perform (N - Z)(N + 1) comparisons. (N - Z)(N + 1) + 2N < (N - Z)(2N - Z) when N is significantly larger than Z.

When there are relatively many zeros in range, your array formula would be more efficient. Otherwise, my 2-extra-cells approach would be more efficient. None of which may matter if the values in range seldom change, in which case efficiency becomes far less important than clarity. That raises the question: are array formulas clear? Depends on who'd need to maintain the workbook.

OK I need to refine my question a bit. I've attached an example. I would like to ignore zeros in other columns, not the data being ranked. I've been trying to use IF statements to account for this but keep getting formula errors. Please see attached.

So I used hemesh's formula and it seems to work great in identifying the correct number of zero values. Now, I need to be able to list the corresponding market name next to the matching values. My index formula has some problems in a couple of cases. Please see column F in the attached.

Wow! I cannot express how much I appreciate your help! Thank you, thank you so much! I could not have done this without your help.

While the formula worked for the sample data set given, when I applied the formula to my full data set, which contains a lot more zero value situations, the formula stopped working correctly. I'm not sure what the -5 reference refers to and am wondering if that could be the issue. My formulas are tied to data from a pivot table and my formulas have to be dynamic and work for constantly changing scenarios. Thanks for your help.

hemesh, yes I changed the references to reflect my data set. When I copied the formula and applied it to the sample worksheet it worked fine. But when I copied it in my actual file and changed the references to reflect my real data set it gave incorrect market names. I did this numerous times and checked my references. I don't understand why it wouldn't work the same with a larger data set.

I added more data to simulate the file I'm working on. These are the same rows and columns used in my file. I hid blank rows for screen space. Column E shows the calculated results. Not sure why the additional rows would make a difference. Thanks for your help.

hemesh, thanks for explaining; I didn't understand that part of the formula. Now it makes sense and my formulas are PERFECT. Many thanks!