How do I prevent an array formula from displaying '#NUM!'?

[videozvideoz]

Apologies, I did post this within another thread but I need an urgent answer so have raised a seperate thread for it.

How do I make my array formula not say "#NUM!"? I'd either like it to report "0" or just be blank

{=IF(ROWS(E$28:E28)>$E$2,"",INDEX(Available,SMALL(IF(Names=$C$2,IF((Available<>0)*($C$29>=Next_Date_Available),ROW(Names))),ROWS(E$28:E28))))}

 

[shift-del]

Hello

Apologies, I did post this within another thread but I need an urgent answer so have raised a seperate thread for it.

How do I make my array formula not say "#NUM!"? I'd either like it to report "0" or just be blank

{=IF(ROWS(E$28:E28)>$E$2,"",INDEX(Available,SMALL(IF(Names=$C$2,IF((Available<>0)*($C$29>=Next_Date_Available),ROW(Names))),ROWS(E$28:E28))))}

This part IF(ROWS(E$28:E28)>$E$2,"", is designed to do exactly what you want.
When you say that doesn't work, then the value in cell E2 is calculated to high. E2 must be equal to the maximum value calculated inside the SMALL() function.

 

[videozvideoz]

The formula is copied down until #NUM! is displayed. E2 cannot be changed - the number is correct. As the formula acts as a lookup, I cannot just delete the #NUM! values as if the criteria changed in C2 then it may well turn into a value

 

[shift-del]

E2 cannot be changed - the number is correct.

It sounds like E2 is not calculated but an input.

E2 should be calculated like this:

=SUM(--((Names=$C$2)*(Available<>0)*($C$29>=Next_Date_Available)))

 

[videozvideoz]

E2 is as follows:

=SUMPRODUCT(--(Names=C2),--(Available<>0))

 

[shift-del]

As I thought, the calculation of E2 is not correct. This part is missing:

($C$29>=Next_Date_Available)

 

[videozvideoz]

Excellent, that makes perfect sense now! Thanks very much for your help