IFERROR techniques for excluding certain values from results 6

We are often in a position where we wish to exclude certain values from an array of results before passing that array to another function.

For example, a common, practical situation is that of finding the minimum value from a range whilst excluding zeroes. This can be done in several ways, for example using an array formula:

=MIN(IF(A1:A10<>0,A1:A10))

or, if we have Excel 2010 or later, using AGGREGATE:

=AGGREGATE(15,6,A1:A10/(A1:A10<>0),1)

(Note that we must use parameter 15 (SMALL) here, since with parameter 5 (MIN) AGGREGATE does not accept anything other than an actual worksheet range for its reference. With parameters 14-19, however, we can first manipulate any ranges before processing. We can also use arrays derived from other functions within AGGREGATE, as well as array constants, neither of which is possible with parameters 1-13.)

All this is very well, but sometimes the array containing zeroes in question is not a simple worksheet range but, for example, an array we have generated from a series of calculations via one or more functions. In these cases, and especially if the formula is a particularly long one, repeating the clause may result in even longer, unwieldy constructions, not to mention the double calculation that we are forcing Excel to compute, e.g.:

=MIN(IF([a_very_long_formula]<>0,[a_very_long_formula],"")

Here’s a practical example:

IFERROR techniques for excluding certain values from results

The formula in H2 is:

=MIN(SUMIFS(F2:F13,A2:A13,{"Mike","John","Alison"},B2:B13,"A",C2:C13,"B",D2:D13,"C",E2:E13,">="&DATEVALUE("27/08/2014"),E2:E13,"<="&DATEVALUE("29/08/2014")))

i.e. is calculating the total sales from column F – where those criteria are satisfied – for each of Mike, John and Alison, and then taking the minimum of those values.

Clearly this will resolve to:

=MIN({5,0,4})

and so the result is 0.

But what if we want to find the minimum excluding zero? The standard technique outlined above using a repetition of the clauses would be:

=MIN(IF(SUMIFS(F2:F13,A2:A13,{"Mike","John","Alison"},B2:B13,"A",C2:C13,"B",D2:D13,"C",E2:E13,">="&DATEVALUE("27/08/2014"),E2:E13,"<="&DATEVALUE("29/08/2014"))<>0,SUMIFS(F2:F13,A2:A13,{"Mike","John","Alison"},B2:B13,"A",C2:C13,"B",D2:D13,"C",E2:E13,">="&DATEVALUE("27/08/2014"),E2:E13,"<="&DATEVALUE("29/08/2014"))))

which is perfectly correct, though certainly a little unwieldy-looking.

Also, although that calculation in particular is not at all resource-intensive, imagine if we had some other construction (perhaps an array formula), repeated over several thousand lines. The double-calculation of the main clause would effectively double the amount of work Excel has to do in order to compute these results.

And here’s where we might start to wish that Excel had introduced an IFZERO function similar to IFERROR…

And that thought might just lead us to the following (now necessarily entered as an array formula):

=MIN(IFERROR(1/(1/(SUMIFS(F2:F13,A2:A13,{"Mike","John","Alison"},B2:B13,"A",C2:C13,"B",D2:D13,"C",E2:E13,">="&DATEVALUE("27/08/2014"),E2:E13,"<="&DATEVALUE("29/08/2014")))),""))

What’s happening here then?

Well, let’s break it down step-by-step. We know what the SUMIFS part resolves to, so we will have:

=MIN(IFERROR(1/(1/({5,0,4})),""))

which is, moving one step further along the calculation chain:

=MIN(IFERROR(1/({0.2,#DIV/0!,0.25}),""))

where clearly the #DIV/0! results from the one case of 1/0.

Now, the nice thing is that, if we then “re-reciprocate” these values, so that, effectively, we will return them back to the original values, we get:

=MIN(IFERROR({5,#DIV/0!,4},""))

since Excel resolves the 1/#DIV/0! as being still #DIV/0!.

We can now pass this array to IFERROR as normal (though the array-entry was required to allow this function to operate on all three elements, not just the first), the result now being the desired 4.

We’re not restricted to using this technique where the main formula in question is extremely long, of course. We can use it whenever we wish: it’s just that unless there’s a genuine reason to avoid the standard IF([formula]<>0,[formula],… then we probably shouldn’t.

Perhaps we might also like to use this technique in cases where the formula contains a range reference which is so long as to make its repetition, again, a little unwieldy-looking. For example, the common situation of wanting to have VLOOKUP return a blank, and not zero as it is wont to do when it comes across an empty cell:

=IF(VLOOKUP(A1,'C:\Documents and Settings\Long_Filepath_Name1\Long_Filepath_Name2\Long_Filepath_Name3\[External_Workbook_with_Ridiculously_Long_Name.xlsx]Sheet1'!$A$1:$B$10,2,0)=0,"",VLOOKUP(A1,'C:\Documents and Settings\Long_Filepath_Name1\Long_Filepath_Name2\Long_Filepath_Name3\[External_Workbook_with_Ridiculously_Long_Name.xlsx]Sheet1'!$A$1:$B$10,2,0))

can at least be made a bit more manageable-looking:

=IFERROR(1/(1/VLOOKUP(A1,'C:\Documents and Settings\Long_Filepath_Name1\Long_Filepath_Name2\Long_Filepath_Name3\[External_Workbook_with_Ridiculously_Long_Name.xlsx]Sheet1'!$A$1:$B$10,2,0)),"")

though of course one might argue that the use of Name Manager here would be a simpler solution.

We can also employ this method in cases other than that for excluding zero. All we need to do is to manipulate the formula such that our desired exclusion will resolve to zero prior to passing to IFERROR(1/(1/…

For example, to find the minimum from A1:A10 excluding the value 3, we would could use the array formula:

=MIN(IF(A1:A10<>3,A1:A10))

though also (again, array-entered):

=MIN(IFERROR(1/(1/(A1:A10-3))+3,""))

Clearly here this would be overdoing things somewhat: the array formula is perfectly fine (and brief). However, I used this merely as an example, and should we be dealing with a much larger construction, as in the SUMIFS case above, then it may well be worth employing this technique.

Indeed, the main reason that I started to explore this method was not so much so that I could use it as a rather eccentric novelty in place of standard constructions (as in the MIN(IF(… example above). Rather I found myself often generating large arrays from large formulas containing several functions and wanting to then exclude certain values from those arrays before passing to the final, resolving function.

Here’s another example, even more surprising-looking perhaps:

=MIN(IFERROR(POWER(SQRT(A1:A10),2),""))

array-entered, will give precisely the same result as:

=MIN(IF(A1:A10>=0,A1:A10))

i.e. return the minimum value from A1:A10 excluding negative numbers. The point being to take advantage of the fact that only negative numbers will cause SQRT to error.

Again, I would like to emphasize that I would not choose to use this technique in this actual situation: I merely use this set-up to demonstrate the idea. Readers might like to see a case in which I employed this method just recently, where in that case it could be argued that it was fully justifiable (at least in the sense that it avoided repetition of a quite long main clause):

http://www.ozgrid.com/forum/showthread.php?t=190636

Of course, people seeing that post for the first time, and trying to comprehend it, might argue that its very abstruseness outweighs the small merit of having an abbreviated formula, but then I hope that even those people might take something positive from the construction, if nothing other than having the sensation of having witnessed something original in Excel!

6 comments

  1. IFERROR(1/(1/… is real beauty.. send data… reverse back to original data but still catching zeros or any criteria as div/0!!

    Great!!

  2. Hello XOR!

    What if I need an array without 0s of only neg/pos values (for example, for a chart?) without next summarising, or counting min/max exc.

    For example, I have a 1-d source array of values like {10,-5,5,-3,12,-8} and want to get only positive {10,5,12} or negative {-5,-3,-8} values.

    Is there are solution?

  3. @Maxim

    If each of the entries in your array is unique, you can use simply:

    MODE.MULT(IF(Arry1>0,{1;1}*Arry1))

    where Arry1 is your array, e.g.:

    {10,-5,5,-3,12,-8}

    Note that if Arry1 is instead a column-vector, i.e.:

    {10;-5;5;-3;12;-8}

    then the unit matrix in the above needs to be transposed, i.e. to:

    {1,1}

    If the entries are not unique, you can use:

    MODE.MULT(IF(TRANSPOSE(FREQUENCY(Arry1,Arry1)),IF(Arry1>0,{1;1}*Arry1)))

    For example, if Arry1 was:

    {1,2,10,-5,5,10,-3,12,-8,12}

    then the above would return:

    {1;2;10;5;12}

    Regards

  4. =IFERROR(MODE(array),"")

    What should i add with this formula to display the highest repetitive value in the array

  5. @Sid Elivera

    Can you clarify? You mean if more than one value shares the highest frequency then return the highest only?

    Can you give a small example with e.g. 10 numbers?

    Regards

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s