Formula contains and ERROR???

[Legacy 259187]

Hello, I am trying to displace 3 different results, 0,1 or "", based on 4 arguments. The formula I am trying to do this with is =IF(AND(B$2>=Data!$L2,B$2<=Data!$W2),1,"",IF(AND (B$2>=Data!$M2,B$2<=Data!$P2),0,"")). Excel is telling me I an error. I must be missing something. This is for a gantt chart that I want to display results in a way that the chart tool cant help me with so I am having to try to build this from scratch. Looks like I have hit a wall. any help to point out the error here would be most appreciated.

 

[Andrew Poulsom]

Possibly, but you haven't posted any sample data and the expected results.

 

[Legacy 259187]

Gantt Chart	T <tbody> </tbody><colgroup><col></colgroup>	W	T	F	S	S	M	T	W	T	F
Dateline	26	27	28	29	30	31	01	02	03	04	05
CA01-07 Wall Sub-Module - Summer 2 <tbody> </tbody><colgroup><col style="text-align: center;"></colgroup>	""	""	1	1	1	1	0	0	0	0	0
FAB.100 Sub Assembly 100 <tbody> </tbody><colgroup><col style="text-align: center;"></colgroup>	""	""	1	1	1	0	0	0	""	""	""
S2-01-07 FAB.100 <tbody> </tbody><colgroup><col style="text-align: center;"></colgroup>	""	""	1	1	1	2	2	2	2	""	""
Final Final Assembly <tbody> </tbody><colgroup><col style="text-align: center;"></colgroup>	""	""	""	""	""	1	0	0	0	0	0
S2-01-07 Final <tbody> </tbody><colgroup><col style="text-align: center;"></colgroup>	""	""	""	1	0	0	0	0	0	""	""
CA01-07 Wall Sub-Module - Vogtle 3 <tbody> </tbody><colgroup><col style="text-align: center;"></colgroup>	1	1	1	0	0	0	0	0	""	""	""
FAB.100 Sub Assembly 100 <tbody> </tbody><colgroup><col style="text-align: center;"></colgroup>	1	1	1	1	0	0	0	0	0	""	""
V3-01-07 FAB.100 <tbody> </tbody><colgroup><col></colgroup>	""	1	1	1	1	0	0	0	""	""	""

<tbody>
</tbody>

 

[Legacy 259187]

The 1 stands for progression, 2 for actual, 0 for baseline and "" are to be populated as blanks. all of which will be conditionally formatted with colors of my choosing. the items orders in the first column are subject to change positions and I need the formula to correctly follow and place the appropriate 0,1,2,"" in the correct cells when data is updated at the source. This is why I need on standing formula to paste throughout the grid.

 

[Andrew Poulsom]

Does this work for you?

=IF(OR(LEFT(A1,2)="S2",LEFT(A1,2)="V3"),IF(AND(C$2>=Data!$M3,C$2<=Data!$X3),1,IF(AND(C$2>=Data!$X3,C$2<=Data!$P3),0,"")),IF(OR(ISNUMBER(FIND("Assembly",A1)),ISNUMBER(FIND("CA0",A1))),IF(AND(C$2>=Data!$V4,C$2<=Data!$W4),1,IF(AND(C$2>=Data!$W4,C$2<=Data!$O4),2,"")),""))

 

[Legacy 259187]

After pasting the formula across the chart with a few mods to indicate the correct cells, I am getting all blanks. The 0,1,2's are not populating at all anymore. On a positive note, I am not getting any errors. Everything looks like it should as far as the original formulas are concerned. With no errors I do not see where the problem could be.

 

[Andrew Poulsom]

You can use the Evaluate Formula tool to see what's happening.

 

[Legacy 259187]

Sir, you are a genius. I had to install some $'s to lock down the column so nothing changed across the grid and toss a few spaces into the key words. eveything is A+ now. Thank you for all your help. My report is now ready to be presented to production. Here is what I had to change up if you are curious... =IF(OR(LEFT($B4,6)=" S2",LEFT($B4,6)=" V3"),IF(AND(C$2>=Data!$V2,C$2<=Data!$W2),1,IF(AND(C$2>=Data!$W2,C$2<=Data!$O2),2,"")),IF(OR(ISNUMBER(FIND("Assembly",$B4)),ISNUMBER(FIND("CA0",$B4))),IF(AND(C$2>=Data!$M2,C$2<=Data!$X2),1,IF(AND(C$2>=Data!$X2,C$2<=Data!$P2),0,"")),""))

 

[Andrew Poulsom]

I'm not sure how LEFT($B4,6) can equal " S2" (3 characters), but I'm glad you got it working.

 

[Legacy 259187]

The Data source exported the "S2"/"V3" with 4 spaces in front and my chart actually begins at B4. A:A is used to calculate the delta to tell up front how many day behind or ahead of schedule each item is. B1-B3 are used to base the delta from todays date and place the Data dateline in the correct location on the chart plus header labeling

 

[Andrew Poulsom]

Yes, but you only have one leading space in " S2", not 4.