INDEX, IF with AND causing #NUM! error

**cedarhill** · 03-10-2009, 11:48 AM

My formula to add a row to a worksheet worked fine, except for the fact that it was including items with 0%. I need items with >0% but <=50%.

Here's the code that sorta works...

Please Login or Register  to view this content.

So I added an AND condition to my IF statement, as follows:

Please Login or Register  to view this content.

...and I get a $NUM! error. I don't understand what problem I've caused since I'm usually pretty comfortable with the IF(AND combination.

I've attached the file.

**NBVC** · 03-10-2009, 12:40 PM

Try perhaps:

Please Login or Register  to view this content.

**cedarhill** · 03-10-2009, 12:55 PM

Yes, it worked perfectly. Now comes the embarrassing part; I'm not familiar with the use of the " * " part in this situation.

You probably don't have time to give me a lesson, but can you direct me to somewhere I can get an understanding of why/when you would use this and what it specifically means. Obviously it fulfills the need that I thought my IF(AND statement was supposed to take care of but didn't.

Regardless, thanks very much.

**NBVC** · 03-10-2009, 01:03 PM

When dealing with Array formulas, the * acts like the AND() function... whereas the + acts like the OR() function... the AND() and OR() functions don't work in Array formulas...

**martindwilson** · 03-10-2009, 01:11 PM

well i thought
=IF(ROW($B3)-ROW($B$3)+1>$J$1,"",INDEX(Master!$B$1:$B$1000,SMALL(IF(Master!$X$3:$X$1000<=0,99999,IF(Master!$X$3:$X$1000<=0.5,ROW($A$3:$A$1000),99999)),ROW($B3)-ROW($B$3)+1)))
only put it up coz its taken me 1/2 hour to puzzle out

**NBVC** · 03-10-2009, 01:14 PM

Originally Posted by martindwilson

well i thought
=IF(ROW($B3)-ROW($B$3)+1>$J$1,"",INDEX(Master!$B$1:$B$1000,SMALL(IF(Master!$X$3:$X$1000<=0,99999,IF(Master!$X$3:$X$1000<=0.5,ROW($A$3:$A$1000),99999)),ROW($B3)-ROW($B$3)+1)))
only put it up coz its taken me 1/2 hour to puzzle out

The nested if is another way.... for the AND() option.. but then you are limited by number of nested functions....