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]

Hi,
You could test a simple 8 row lookup table ...

 

[Mr_Phil]

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

The best way I have found to write an if statement is to break it down into three parts and only chew on one part at a time.

If(Test, True, False)

So I would fire up excel and put that in the spot I wanted. Double click the "test" and write in your test then hit enter. Make sure the test is as yes/no sort of thing. Like, A2 does not equal six. You will get either TRUE or FALSE. Once you get a result of true you know the test works. Then double click the TRUE and write in the result you need to see when it is true. Test again, instead of TRUE you should get the actual result you want. Then copy the If(Test, True, False) and double click the word false in the cell. You'll paste the new IF template right there. So it goes, test, true result or new if with new test. I hope that made sense. It's easy to get lost in nested formulas. I like lookups better myself.

 

[mjacquot82]

Here is the bigger picture that i am working on. The distribution center arrival time is simple to calculate is it always starts @ 6:30 and depending on how many pieces are arriving knowing they can receive 5250 pcs / hour its simple math.

I can figure out the start load time for the first warehouse as its simply the receiving time minus the time require to travel to the distribution center. It is also worth mentioning that when 2 trucks arrive @ once there will be trucks arriving prior to its receiving time. Where i struggle is when i get to second warehouse. When the config is 1 truck at a time i need to figure out the load time of the last truck first then work my way back to the very first truck.

If it wasn't for the different loading configs i could figure it out, but its just beyond what i am capable of doing.

It goes without saying that the PCS Arriving column changes every day ( i simply take the total pcs per warehouse and divide it by the # of trucks ). So also need to try and make this tool fluctuate with changes in PCS count.

Also i need to mention that trucks are to be schedule to arrive an hour prior to receiving time.

Are you able to offer any help with this ?

 

[mjacquot82]

The best way I have found to write an if statement is to break it down into three parts and only chew on one part at a time.

If(Test, True, False)

So I would fire up excel and put that in the spot I wanted. Double click the "test" and write in your test then hit enter. Make sure the test is as yes/no sort of thing. Like, A2 does not equal six. You will get either TRUE or FALSE. Once you get a result of true you know the test works. Then double click the TRUE and write in the result you need to see when it is true. Test again, instead of TRUE you should get the actual result you want. Then copy the If(Test, True, False) and double click the word false in the cell. You'll paste the new IF template right there. So it goes, test, true result or new if with new test. I hope that made sense. It's easy to get lost in nested formulas. I like lookups better myself.

I think i understand what you are saying, i will have to try that next time i find time to play around with it !

Thank you !

 

[mjacquot82]

Here is the bigger picture that i am working on. The distribution center arrival time is simple to calculate is it always starts @ 6:30 and depending on how many pieces are arriving knowing they can receive 5250 pcs / hour its simple math.

I can figure out the start load time for the first warehouse as its simply the receiving time minus the time require to travel to the distribution center. It is also worth mentioning that when 2 trucks arrive @ once there will be trucks arriving prior to its receiving time. Where i struggle is when i get to second warehouse. When the config is 1 truck at a time i need to figure out the load time of the last truck first then work my way back to the very first truck.

If it wasn't for the different loading configs i could figure it out, but its just beyond what i am capable of doing.

It goes without saying that the PCS Arriving column changes every day ( i simply take the total pcs per warehouse and divide it by the # of trucks ). So also need to try and make this tool fluctuate with changes in PCS count.

Also i need to mention that trucks are to be schedule to arrive an hour prior to receiving time.

Are you able to offer any help with this ?

Here is a visual, this is what i am trying to fill.

 

[Mr_Phil]

I think i understand what you are saying, i will have to try that next time i find time to play around with it !

Thank you !

I’m away from the computer now. But when I get back maybe I can make a “How to” video and put it up. It’s not really hard, and it works for me. But, I am not good at expressing clearly without a lot of rewrites. Hope it all comes together for you.

 

[James006]

Hi again,
Based on you initial arborescence, you are actually dealing with potentially 8 different possibilities.
Is that correct (and confirmed) or not ?

 

[mjacquot82]

Hi again,
Based on you initial arborescence, you are actually dealing with potentially 8 different possibilities.
Is that correct (and confirmed) or not ?

Yes i believe you are correct.

 

[mjacquot82]

I’m away from the computer now. But when I get back maybe I can make a “How to” video and put it up. It’s not really hard, and it works for me. But, I am not good at expressing clearly without a lot of rewrites. Hope it all comes together for you.

I have not yet had the time to try your idea, but thank you for your suggestion.