Stop Array formula on first error

[sherbetdab]

=IF(ISERROR(INDEX(Units!$C:$C, SMALL(IF(COUNTIF($E$1, Units!A:A)*COUNTIF($E$4, Units!B:B),ROW(Units!$C:$C)-MIN(ROW(Units!$C:$C))+1), ROW(A1)), COLUMN(A1))),0,(INDEX(Units!$C:$C, SMALL(IF(COUNTIF($E$1, Units!A:A)*COUNTIF($E$4, Units!B:B),ROW(Units!$C:$C)-MIN(ROW(Units!$C:$C))+1), ROW(A1)), COLUMN(A1))))

I have the above array formula where E1 and E4 are values in drop down boxes.

I have the formula in about 60 cells as the results are occasionally that high.

Most of the time it only returns 6 to 14 results.

Is there a way to stop the formula in the the rows after the last result?

I'm guessing this would cut down on calculation time.

Thanks in advance.

[AlKey]

You can't stop formula but you can hide errors by adding IFERROR function


=IFERROR(IF(ISERROR(INDEX(Units!$C:$C, SMALL(IF(COUNTIF($E$1, Units!A:A)*COUNTIF($E$4, Units!B:B),ROW(Units!$C:$C)-MIN(ROW(Units!$C:$C))+1), ROW(A1)), COLUMN(A1))),0,(INDEX(Units!$C:$C, SMALL(IF(COUNTIF($E$1, Units!A:A)*COUNTIF($E$4, Units!B:B),ROW(Units!$C:$C)-MIN(ROW(Units!$C:$C))+1), ROW(A1)), COLUMN(A1)))),"")

[XOR LX]

@Alkey Bit confused by your suggestion. I presumed the OP already an error trap (to return 0, not a blank) in there in the form of an IF(ISERROR)? (Guess the reason it's not IFERROR is that it needs to work for 2003 as well?)

Regards

[AlKey]

@Alkey Bit confused by your suggestion. I presumed the OP already an error trap (to return 0, not a blank) in there in the form of an IF(ISERROR)? (Guess the reason it's not IFERROR is that it needs to work for 2003 as well?)

Regards

You maybe right. But from the OP description the formula is working but it still producing an error when there are no more records. So, adding IFERROR function should take care of it. As far as compatibility with 2003 I guess will find out soon after OP reply.

[XOR LX]

I don't see any part of the original post that says they're still getting an error?

I think they're less concerned with hiding an error than with the question of whether it's possible to have a series of formulas which will only calculate when necessary, i.e if their relative row number is less than or equal to the number of returns to be given.

@sherbertdab Anway, if that's the case, I think that you'd be better advised not to use entire columns as your range references in an array formula - unlike other in-built functions, array formulas will be calculated for the entire range you specify, so I would strongly suggest replacing them, either with static ranges up to a suitable limit, or, if necessary, with dynamic, offset ranges.

Regards

[sherbetdab]

Thanks Guys.

When it returns an error I get a 0. Which is when it has found the last possible result.

What I meant was can I replace the 0 with some sort of stop command?

Sorry for the confusion.

[daddylonglegs]

Do you need the formula to work in Excel 2003? If not you can simplify by using IFERROR and that would probably reduce the calculation time, e.g.

=IFERROR(INDEX(Units!$C:$C, SMALL(IF(COUNTIF($E$1, Units!A:A)*COUNTIF($E$4, Units!B:B),ROW(Units!$C:$C)-MIN(ROW(Units!$C:$C))+1), ROW(A1)), COLUMN(A1)),0)

can zero be a valid result returned from column C.....or would you only get that in case of error? If the latter then you could just add an IF to look at the previous cell, e.g. assuming you are putting the first formula in H2 and copying down you could use this

=IF(H1=0,0,IFERROR(INDEX(Units!$C:$C, SMALL(IF(COUNTIF($E$1, Units!A:A)*COUNTIF($E$4, Units!B:B),ROW(Units!$C:$C)-MIN(ROW(Units!$C:$C))+1), ROW(A1)), COLUMN(A1)),0))

adjust the H1 to be the cell above the first formula

so once you get a zero every subsequent row would also be zero, without running the whole formula each time [the IFERROR(INDEX part won't even be evaluated in those cases]

[XOR LX]

Since you seem to be concerned with, as you put it, "calculation time", I think you'll find that following the advice I gave in my last post about not referencing entire columns (A:A, C:C, etc.) in array formulas will be of significant benefit.

Regards

[sherbetdab]

Since you seem to be concerned with, as you put it, "calculation time", I think you'll find that following the advice I gave in my last post about not referencing entire columns (A:A, C:C, etc.) in array formulas will be of significant benefit.

Regards

Being a relative newbie to excel, no formal training, google and the forums are my friend, I never realise what you meant in your first reply.

I have now put a range in for all row references and it has done the trick.

It's calculating in no time at all now.

Thank you very much.

[XOR LX]

You're welcome. Glad I could help.

[sherbetdab]

This is Solved.

How do I mark the post title as solved?