Replacing Small Function with xSmall....

Hi again,

Of course we can do this with a simple extra clause using worksheet functions:

=SMALL(A1:A10,k+COUNTIF(A1:A10,0))

will return the kth smallest value from A1:A10 excluding zeroes.

Regards

Originally Posted by Vikas_Gautam

but countif don't recognize arrays..

Sorry, Vikas. What do you mean by this?

You mean arrays which are not an actual worksheet range reference, but e.g. the array produced by intermediate functions within a formula?

Can you give an example?

Regards

Sure, but you're not restricted to using COUNTIF in these constructions.

Though I also don't think the example you've given is a very good one (the returns are either 0 or 1) since (array-entered):

=SMALL(--(A1:A10=B1),k+COUNTIF(A1:A10,"<>"&B1))

gives you the kth smallest value excluding the zeroes (which will in any case be a 1 in this example, assuming such a kth smallest non-zero value exists.)

Basically, I think that there's just about always a way to achieve this extraction using additional function(s) within the formula.

Regards

Actually, I very rarely use the COUNTIF approach myself. I was simply demonstrating one possibility. My own way, given such an array, is to use (array-entered):

=SMALL(IFERROR(1/(1/({1;0;2;0;0;0;3;0;4;0})),""),k)

Of course, this works when excluding zeroes, but not necessarily any other value.

However, since excluding zeroes is quite a common requirement in Excel (I think that Microsoft should get round to developing an IFZERO() function simiiar to IFERROR()) then this approach is quite useful, not to mention nice and short!

Regards

Not sure why that's such a "limitation", but if you insist you can add an INDEX:

=SMALL(INDEX(IFERROR(1/(1/({1;0;2;0;0;0;3;0;4;0})),""),,),1)

By the way, I should say I do often use this approach for excluding values other than zero as well, especially in cases where you have an extremely long clause in a formula which you would otherwise have to repeat, e.g. instead of:

=MIN(IF([results of large formula]<>0,[results of large formula]))

we can use:

=MIN(IFERROR(1/(1/([results of large formula])),""))

But we can also adapt it slightly to exclude values other than zero, e.g. to find the minimum excluding the value 2:

=MIN(IFERROR(1/(1/([results of large formula]-2))+2,""))

And even get a little more creative. e.g. to find the minimum excluding negative numbers:

=MIN(IFERROR(SQRT([results of large formula])^2,""))

(Providing rounding issues are not a factor.)

Regards

Originally Posted by Vikas_Gautam

Sorry XOR..

Please Login or Register  to view this content.

will not perform has array..

Formula:

Please Login or Register  to view this content.

No it doesn't!! Show me a workbook or screenshot of Evaluate Formula which gives that!

=SMALL(INDEX(IFERROR(1/(1/({1;0;2;0;0;0;3;0;4;0})),""),,),1)

becomes:

=SMALL(INDEX(IFERROR({1;#DIV/0!;2;#DIV/0!;#DIV/0!;#DIV/0!;3;#DIV/0!;4;#DIV/0!},""),,),1)

which is:

=SMALL(INDEX({1;"";2;"";"";"";3;"";4;""},,),1)

which is:

=SMALL({1;"";2;"";"";"";3;"";4;""},1)

as required.

No idea what you've been doing!

Regards

Not sure I understand. What's wrong with the current solutions you have? Look perfectly fine to me.

Are you sure you're not just trying to avoid array formulas for the sake of it?

This reminds me of the ridiculous situation I was in about a year ago (and which I told you about) when I was adding several extra INDEX functions in an attempt to avoid array-entry...

Regards

If you absolutely insist:

=INDEX(C1:C14,SMALL(INDEX(IFERROR(N(INDEX(1/(1/(COUNTIFS(F1,A1:A14,F2,B1:B14)*ROW(B1:B14))),,)),""),,),F3))

though the triple coercion required from the three extra function calls (two INDEX plus one N), not to mention the IFERROR, frankly makes this a slightly ridiculous solution.

You see what I mean when I talk about "avoiding" array formulas now?

Regards

But there's nothing wrong with array formulas!!

Regards

Originally Posted by XOR LX

But there's nothing wrong with array formulas!!

+1

It's painful to see the extent to which some folks go to avoid array formulas!

Not having array formulas available is like a carpenter showing up on the job without a hammer.

Originally Posted by Vikas_Gautam

if he just want to calculate nth non-zero small value.. he will put in a formula and enjoy the result..

Originally Posted by ragulduy

Whilst you are correct that the formula auditing is only useful after the array formula has been entered, you could use the same logic for a UDF, it is only useful (for the person to put in a formula and enjoy the result) once it has been entered into the vba editor (which the person can't do as they don't know how!).

Agreed with ragulduy. The whole premise on which this idea is based seems to boil down to the idea that using a UDF is far simpler for an average Excel user than is committing an array formula.

Since I started using a clear, descriptive footer in my posts pointing out how array formulas should be entered (as do the majority of other long-term contributors), I've barely had a single person come back to me complaining that they couldn't get it to work.

The facts are:

1) Array formulas are not difficult to enter with a bit of guidance

2) UDFs are no more self-evidently simple than array formulas

3) Use of array formulas should not in any way be avoided/discouraged

Array formulas are something that we should actively promote in our posts, not seek for ways to circumvent their use. They are a fascinating and necessary part of this wonderful tool we call Excel, and we, as regular forum members, should do what we can so that more people may first understand, explore and then enjoy this feature.

Regards