Multiple IF conditions and Arrays

[loosenut]

Hi,

I am trying to extract the type of activity (column I) a specific drilling rig (column Z) will be performing between two dates (columns AP and AO). Essentially the arguements should be for all rows containing the rig name (z2) in column A, whose end date (column J) is <=AP and start date (column K) >=AO, then tell me the type of activity (column I), otherwise 0). Am I close, is there a better way to do this? The answer seems to always report "0" even if there is data?


{=IF(AND(($A$2:$A$1500=Z2),($J$2:$J$1500<=AP$1),($K$2:$K$1500>=AO$1))=TRUE,$I$2:$I$1500,0)}

Thanks for your help!

Loosenut

 

[NBVC]

Try evaluating the formula with just Enter. Your sample shows the curly parenthesis which leads me to believe you used Ctrl+Shift+Enter. That worked for me.

 

[Aladin Akyurek]

Hi,

I am trying to extract the type of activity (column I) a specific drilling rig (column Z) will be performing between two dates (columns AP and AO). Essentially the arguements should be for all rows containing the rig name (z2) in column A, whose end date (column J) is <=AP and start date (column K) >=AO, then tell me the type of activity (column I), otherwise 0). Am I close, is there a better way to do this? The answer seems to always report "0" even if there is data?


{=IF(AND(($A$2:$A$1500=Z2),($J$2:$J$1500<=AP$1),($K$2:$K$1500>=AO$1))=TRUE,$I$2:$I$1500,0)}

Thanks for your help!

Loosenut

Care to post 5 rows from each range the formula you have refers to along with the expected result(s)?

 

[loosenut]

Almost Works!

Ok, So I typed in the adjustments to cell AB2, and it worked (output was "TRUE")! EXCEPT, then I tried to copy the formula down the column (cell AB3), the answered changed to "FALSE", even though it is the identical formula (I copied it by cell, and also copied it by entering the formula, selecting it, copying it and pasting the formula in a new cell). All the cells have absolute references. Is my computer possessed??? The clue is that when I copied it across the row, the answer was replicated "TRUE". It's the same forumla - shouldn't it be the same whether copied down a column or accross a row??

So Close!

Loosenut

Cell AB2: =IF(AND(('Fleet List Input UDW'!$A$2:$A$1500='Fleet List Input UDW'!$AH$25),('Fleet List Input UDW'!$J$2:$J$1500<='Fleet List Input UDW'!$BW$1),('Fleet List Input UDW'!$K$2:$K$1500>='Fleet List Input UDW'!$BW$1))=TRUE,TRUE,FALSE)
Output: TRUE

Cell AB3: =IF(AND(('Fleet List Input UDW'!$A$2:$A$1500='Fleet List Input UDW'!$AH$25),('Fleet List Input UDW'!$J$2:$J$1500<='Fleet List Input UDW'!$BW$1),('Fleet List Input UDW'!$K$2:$K$1500>='Fleet List Input UDW'!$BW$1))=TRUE,TRUE,FALSE)
Output: FALSE

Cell AC2: =IF(AND(('Fleet List Input UDW'!$A$2:$A$1500='Fleet List Input UDW'!$AH$25),('Fleet List Input UDW'!$J$2:$J$1500<='Fleet List Input UDW'!$BW$1),('Fleet List Input UDW'!$K$2:$K$1500>='Fleet List Input UDW'!$BW$1))=TRUE,TRUE,FALSE)
Output: TRUE

 

[loosenut]

One more Clue

Even when I cut and paste the cell that is working, it still changes the answer from "TRUE" to "FALSE". When I paste it back, it turns back to "TRUE"............ The mystery continues....

Thanks for your help!

Loosenut

 

[NBVC]

Seems to be working properly for me.

I used

=IF(AND((Sheet2!$A$5:$A$1504=Sheet2!$AH$29),(Sheet2!$J$5:$J$1504<=Sheet2!$BW$4),(Sheet2!$K$5:$K$1504>=Sheet2!$BW$4))=TRUE,TRUE,FALSE)

I get all Trues or all Falses, when copied down, depending on what I have in the referenced cells and ranges.

I might suggest to try opening another sheet and testing it there (or better yet another workbook). Perhaps, there is a formatting issue?

 

[loosenut]

I think it May be a limitation of the formula

So I copied it into a separate worksheet and tried it again (pasted below). Cell E2 and E3 Should be exactly the same result because it should be checking the whole range. So my conclusion is that something in the if statement only works if the entry in column D, happens to equal the entry in column A in the same row, and the rest of the conditions also match. I'd like it to work so that the entry in column D does not have to be in the same row as column A (i.e. checks the range in A). Let me know if you have any thoughts.

Thanks!

Loosenut

Cell: e2

=IF(AND(($A$2:$A$1504=$D2),($B$2:$B$1504<=E$1),($C$2:$C$1504>=E$1))=TRUE,"TRUE","FALSE")

Cell: e3

=IF(AND(($A$2:$A$1504=$D3),($B$2:$B$1504<=E$1),($C$2:$C$1504>=E$1))=TRUE,"TRUE","FALSE")

Book4.xls
	A	B	C	D	E	F
1	RigName	ActivityStartDate	EndDate		2/28/2007	3/31/2007
2	BelfordDolphin	1/18/2007	8/31/2007	BelfordDolphin	TRUE	TRUE
3	BelfordDolphin	11/29/2006	1/18/2007	BelfordDolphin	FALSE	FALSE
4	BelfordDolphin	8/28/2004	11/29/2006	BelfordDolphin	FALSE	FALSE
5	DeepwaterDiscovery	9/1/2008	9/2/2008	DeepwaterDiscovery	FALSE	FALSE
6	DeepwaterDiscovery	8/1/2006	7/31/2008	DeepwaterDiscovery	TRUE	TRUE
7	DeepwaterDiscovery	9/10/2005	11/4/2005	DeepwaterDiscovery	FALSE	FALSE
8	DeepwaterExpedition	9/1/2008	9/2/2008	DeepwaterExpedition	FALSE	FALSE
9	DeepwaterExpedition	9/3/2006	9/1/2008	DeepwaterExpedition	TRUE	TRUE
10	DeepwaterExpedition	8/2/2006	9/3/2006	DeepwaterExpedition	FALSE	FALSE
11	DeepwaterExpedition	6/23/2006	8/2/2006	DeepwaterExpedition	FALSE	FALSE
12	DeepwaterExpedition	6/18/2006	6/23/2006	DeepwaterExpedition	FALSE	FALSE
13	DeepwaterExpedition	2/3/2006	6/18/2006	DeepwaterExpedition	FALSE	FALSE
Sheet1

 

[NBVC]

Does this work in E2 copied down? :

=IF(SUMPRODUCT(--($A$2:$A$1504=$D2),--($B$2:$B$1504<=E$1),--($C$2:$C$1504>=E$1))>0,"TRUE","FALSE")

I am not if this topic is becoming confusing now: If we still haven't gotten there, perhaps, without discussing formulas, tell us what you are trying to accomplish and what your data looks like to begin with.

 

[loosenut]

Success!

NBVC,

The formula worked like a charm! I now have a beautiful chart of tight the supply of offshore oil rigs is going to be for the next five years (tight). Thanks for sharing your expertise!

Best,

Loosenut

P.S. Any suggestion as to where to find instructions for the advanced syntax you used in your sumproduct formula? (i.e. --) Thanks...

 

[emren]

Try this:

http://www.mrexcel.com/board2/viewtopic.php?t=115792&highlight=