IF OR Statement

[edwardpestian]

I have the following IF statement. I'm simply trying to expand the second IF statement within the MIN function to say IF the range b16:b25 <> "ber" or "iw" I'm trying to say that if the date in A16:A25 and "ber" or "iw" is in the range B16:25, then skip to the next date in the range A16:A25 that doesn't have "ber" or "iw" in column B, and then subtract A15 from that date. If its greater than 90 days then -1, otherwise zero.

{=IF($A15=0,"",IF($A16-$A15>90,-1,IF(MIN(IF($A16:$A25<TODAY(),IF($B16:$B25<>"ber",$A16:$A25),0))-$A15>90,-1,0)))}

The above function returns -1. So adding the OR statement should still return -1 as only one of the conditions needs to be met.

For some reason however, when I add the OR statement, I keep getting a value of zero.

{=IF($A15=0,"",IF($A16-$A15>90,-1,IF(MIN(IF($A16:$A25<TODAY(),IF(OR($B16:$B25<>"ber",B16:B25<>"iw",$A16:$A25),0)))-$A15>90,-1,0)))}

Thanks.

ep

 

[byundt]

AND and OR in array formulas operate on the entire range, not row by row like you want. You need to use * and + instead. Try your array formula like:

 =IF($A15=0,"",IF($A16-$A15>90,-1, -(INDEX(A16:A25,MIN(IF((A16:A25<TODAY())*(B16:B25<>"ber")*(B16:B25<>"iw"),ROW(B16:B25)-15,""))) -$A15>90)))

Brad

 

[edwardpestian]

Not quite working. Rows that contain "ber" or "iw" in column B should not be given a value of negative 1. They should simply be disregarded.

Thanks again.

ep

 

[edwardpestian]

Got it working like this: each row has to have the value "-15" increment by one. row 15 -15, row 16 -16 etc. But it seems there no way to drag down and have the value "-15" increment by one?

=IF($A15=0,"",IF($A16-$A15>90,-1,-(INDEX($A16:$A$25,MIN(IF(($A16:$A$25<TODAY())*($B16:$B$25<>"ber")*($B16:$B$25<>"iw"),ROW($B16:$B$25)-15,"")))-$A15>90)))

Regards,

ep

 

[edwardpestian]

Disregard, its still not working. The rows with BER or IW are still being given a value of negative 1. Basically, these rows should be completely disregarded.

ep

 

[byundt]

Could you post some sample data and expected results that demonstrates the formula not working? In my workbook, the formula finds the first date in column A where the following two conditions apply:
1) Column B (same row) doesn't equal "ber" or "iw"
2) The date in column A is less than 90 days after A15

If you want to increment the -15 by one as you copy the formula down, then just try:

=IF($A15=0,"",IF($A16-$A15>90,-1, -(INDEX($A16:$A$25,MIN(IF(($A16:$A$25<TODAY())*($B16:$B$25<>"ber")*($B16:$B$25<>"iw"),ROW($B16:$B$25)-ROW($A15),"")))-$A15>90)))

Brad

 

[edwardpestian]

Got it working by adding an IF(C5=0,"".

Thanks for your help Brad

ep

=IF($A5=0,"",IF(C5=0,"",IF($A6-$A5>90,-1,-(INDEX($A6:$A$25,MIN(IF(($A6:$A$25<TODAY())*($B6:$B$25<>"ber")*($B6:$B$25<>"iw"),ROW($B6:$B$25)-ROW(A5),"")))-$A5>90))))

 

[edwardpestian]

According to my last post, I was able to get the function to work as expected. I must admit though, I'm not quite following what's happening after the third iF statement. starting with -(index. Could someone please explain to me in plain English?

Thanks.

ep

 

[byundt]

-(INDEX($A6:$A$25,MIN(IF(($A6:$A$25<TODAY())*($B6:$B$25<>"ber")*($B6:$B$25<>"iw"),ROW($B6:$B$25)-ROW(A5),"")))-$A5>90)

In an array formula, a Boolean expression involving a range of cells is resolved into an array of TRUE and FALSE values--one for each row:

($A6:$A$25<TODAY())    the date in column A occurs before today
($B6:$B$25<>"ber")     column B does not equal "ber"
($B6:$B$25<>"iw")      column B does not equal "iw"

When you use TRUE and FALSE in arithmetic expressions, they are converted into 1 (for TRUE) or 0 (for FALSE). So multiplying three arrays of Boolean values is the same thing as AND, only it returns a separate result (1 or 0) for each row

($A6:$A$25<TODAY())*($B6:$B$25<>"ber")*($B6:$B$25<>"iw")

The IF function looks at this array of 1 and 0, and returns either the row number (less 5) or a blank.

IF(($A6:$A$25<TODAY())*($B6:$B$25<>"ber")*($B6:$B$25<>"iw"),ROW($B6:$B$25)-ROW(A5),"")

The MIN function then determines the smallest index number (row number - 5) returned by the IF.

Because the value 5 comes from a ROW function using a relative address for cell A5, as you copy the formula down, it returns 5, 6, 7, etc. This compensates for the fact that the beginning of all the other ranges is also shifting in relative fashion as you copy the formula down.

The INDEX function returns the date from column A on the first row that satisfies the Boolean constraints. From this date, you subtract the date in cell A5 and see if it is greater than 90 days. If so, return a value of TRUE; otherwise return FALSE.

Because the INDEX part of the formula is prefixed by a minus sign, the TRUE and FALSE are converted into -1 and 0.

Brad

 

[edwardpestian]

I think I'm with you, but let me just clarify a couple of items. When the ROW function returns the row number, its actually returning the row number relative to the entire worksheet. For example row 18:

Row 18 is row 18 relative to the worksheet, but only row 13 within the range because the range does not start until row 6 (A6). That is why we subtract 5 from the row number that the IF statement returns: -ROW(A5)

Using the minus sign before the INDEX function converts the TRUE and FALSE to -1 and 0 respectively. This is what returns the -1 value if the boolean constraints are met. Is this correct? Let's say for example, I wanted a value of -2 if the boolean constraints were met. Do I simply multiply the expression by 2?

=IF($A5=0,"",IF(C5=0,"",IF($A6-$A5>90,-1,-(INDEX($A6:$A$25,MIN(IF(($A6:$A$25<TODAY())*($B6:$B$25<>"ber")*($B6:$B$25<>"iw"),ROW($B6:$B$25)-ROW(A5),"")))-$A5>90))*2))

Thanks again for all the help.

ep

 

[byundt]

EdwardPestian,
Your understanding is 100% correct.

Incidently, you can shorten your formula (and eliminate one level of nesting) by using OR function on the first two IF:

=IF(OR($A5=0,C5=0),"",IF($A6-$A5>90,-1, -(INDEX($A6:$A$25,MIN(IF(($A6:$A$25<TODAY())*($B6:$B$25<>"ber")*($B6:$B$25<>"iw"),ROW($B6:$B$25)-ROW(A5),"")))-$A5>90))*2)

Brad