Extremely Long If Statement

[mjacquot82]

Hi everyone, i have tried my best to write the IF statement i need but unfortunately i get lost in it. I believe that the below image depicts accurately what i am trying to write. I though maybe breaking down like this would help ne write it, but still i get lost in the formula. If someone could help me write it or know of a better way to achieve the end goal i would greatly appreciate it.

Thanks in advance

 

[James006]

Yes i believe you are correct.

If you can list all your 8 possibilities in a Reference Table as described in your message #1, then, you can forget the idea of nested Ifs ... and use a simple vlookup() function ...

Hope this will help

 

[Eric W]

Similar to James006's idea of a lookup table, you can use CHOOSE with a truth table. Consider:

Book1
	A	B	C	D	E	F
1
2		Match	Kill Order=1	Truck #=1	Value
3	Case 1	N	N	N	0	ISNUMBER(MATCH(A3,Z1:Z10,0))*4+(KO3=1)*2+(TN3=1)
4	Case 2	N	N	Y	1
5	Case 3	N	Y	N	2
6	Case 4	N	Y	Y	3
7	Case 5	Y	N	N	4
8	Case 6	Y	N	Y	5
9	Case 7	Y	Y	N	6
10	Case 8	Y	Y	Y	7
11
12		*4	*2	*1		CHOOSE(Value+1,"Case 1","Case 2","Case 3","Case 4","Case 5","Case 6","Case 7","Case 8")
Sheet2

The table in A3:D10 is called a truth table, and it has every combination of your three conditions. Now if you consider N=0 and Y=1, and multiply each column by the value in B12:D12, you get the list of values in E3:E10. You can see they are all unique. This is binary counting. The way to convert your three conditions to that value might be something like the formula fragment in F3. I didn't quite see how your posts explained where your values are that you are testing. Once you get the value, you can use a CHOOSE like in F12. Change the "Case 1", etc. to whatever you want to do in that situation. It might actually be better to add another column to the table with the value you want returned. That way you can change the table and not have to change the formulas.

 

[mjacquot82]

Similar to James006's idea of a lookup table, you can use CHOOSE with a truth table. Consider:

Book1
	A	B	C	D	E	F
1
2		Match	Kill Order=1	Truck #=1	Value
3	Case 1	N	N	N	0	ISNUMBER(MATCH(A3,Z1:Z10,0))*4+(KO3=1)*2+(TN3=1)
4	Case 2	N	N	Y	1
5	Case 3	N	Y	N	2
6	Case 4	N	Y	Y	3
7	Case 5	Y	N	N	4
8	Case 6	Y	N	Y	5
9	Case 7	Y	Y	N	6
10	Case 8	Y	Y	Y	7
11
12		*4	*2	*1		CHOOSE(Value+1,"Case 1","Case 2","Case 3","Case 4","Case 5","Case 6","Case 7","Case 8")
Sheet2

The table in A3:D10 is called a truth table, and it has every combination of your three conditions. Now if you consider N=0 and Y=1, and multiply each column by the value in B12:D12, you get the list of values in E3:E10. You can see they are all unique. This is binary counting. The way to convert your three conditions to that value might be something like the formula fragment in F3. I didn't quite see how your posts explained where your values are that you are testing. Once you get the value, you can use a CHOOSE like in F12. Change the "Case 1", etc. to whatever you want to do in that situation. It might actually be better to add another column to the table with the value you want returned. That way you can change the table and not have to change the formulas.

Did you see this ?

 

[Eric W]

I did, however I was unable to relate it to your question in post 1. Where are the 3 (or more?) conditions you're looking at? If that is the table you're trying to fill, what are you trying to fill? The Start Loading Time and Plant Arrival columns? Can you manually calculate a few of the rows, and explain how you got the results? The more different kind of cases, the better.

 

[mjacquot82]

I did, however I was unable to relate it to your question in post 1. Where are the 3 (or more?) conditions you're looking at? If that is the table you're trying to fill, what are you trying to fill? The Start Loading Time and Plant Arrival columns? Can you manually calculate a few of the rows, and explain how you got the results? The more different kind of cases, the betterI

I am sorry for the delay in reply, i have been trying to figure this out on my own but unfortunately i just get lost in it all. To answer one of your questions, it is the start loading time that i am trying to have filled as well as the plant arrival ( which is the easier of the two ).

If the loading config is " 2 " then that means that 2 trucks will load at a time. The calculate what time they need to start i take the receiving time and substract the total of ( time required to load + time required to distribution center from warehouse). If loading config is 2 and there are 4 trucks, then the second set of trucks would start loading 2 hours ( if that is the time required to load ) later than the time prior.

If the loading config is "1 " its trickier, i would take the receiving time of the last truck to be received and work my way back.

Does this make any sense ?

Again thanks for your time