IF to hide an error #NUM!

[Alfredim]

Hi, I have an array:

=IF($D$53:$D$62="";"";INDEX($D$53:$D$62;SMALL(IF($D$53:$D$62<>"";ROW($D$53:$D$62)-ROW($D$53)+1);1)))

but when $D$53:$D$62="" gives #NUM!

How can I go around it?

Thanks

[Sam Capricci]

First off, I'm not good with debugging array formulas especially if all I can see is the posted formula and not the actual spreadsheet but I wonder if you substituted a sum formula for the first part of the array? like =IF(sum($D$53:$D$62)="";"";INDEX($D$53:$D$62;SMALL(IF($D$53:$D$62<>"";ROW($D$53:$D$62)-ROW($D$53)+1);1)))
might work?

[Alfredim]

thank you,

worked like this:
=IF(sum($D$53:$D$62)=0;"";INDEX($D$53:$D$62;SMALL(IF($D$53:$D$62<>"";ROW($D$53:$D$62)-ROW($D$53)+1);1)))

But I give you all the credit. Thanks again!

[Sam Capricci]

Yes, I should have put it in as =0 instead of ="", was only trying with a guess. Glad it worked for you as you adjusted it.

[Alfredim]

Sorry, but is not working!~lol
It's always true. Can I send you the workbook?

[Sam Capricci]

the best thing to do is upload it here with any confidential information removed. As I noted I'm not that good with array formulas so if you upload the doc here others who have better skills than I have with array formulas might see something i don't see. Just use the "go advanced" button below. I always find that saving the doc to my desktop is the easiest way to upload it.

Besides that, i'm not going to be online much longer.

[Alfredim]

array error help.

[Alfredim]

new workbook with no links (45Kb)

thanks for help

[JeteMc]

If I am understanding correctly you want to hide the error. If that is correct then an array formula similar to the following will work:

Please Login or Register  to view this content.

I applied this array formula to Q7, activated it with Ctrl+Shift+Enter, and dragged it across to X7.
Let me know if you have any questions.

[Alfredim]

Sorry, My bad, it works fine If(iserror(...);"";...)
thank you for your help!

[JeteMc]

Your Welcome and thank you for the feedback, glad that things are now working as expected. If the solutions are satisfactory, and if you have not already, please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a nice day.