# Thread: IF Statement based on the IF Statement result

1. ## IF Statement based on the IF Statement result

Hello, I am working on a formula that will run through the below hierarchy. I continue to get the wrong results. I hope I can clearly outline my requirement. This is the process I am trying to follow. [QUOTE]

HTML Code:
```If Profile = S
Is Packaging = TR
Y  IF(Packing="TR",ROUND(\$100/Price/MPQ,0)*MPQ)
N  \$100/Price

If Profile = N
Is Packaging = TR
Y  IF(Packaging="TR",ROUND(\$100/Price/MPQ,0)*MPQ)
Is "Stock Qty" (B2) >= above statement?
Y  IF(Packaging="TR",ROUND(\$100/Price/MPQ,0)*MPQ)
N  MOQ
N  \$100/Price
Is "Stock Qty" (B2) >= above statement?
Y  \$100/Price
N- MOQ ```
If the part is on a TR, then I need to increase the MOQ until \$100 is met. i.e.: \$100/Price/MPQ

Once that formula is ran, I need to get the answer from that and check to see if there is enough stock.

To help your understanding. I sell Microchips. Sometimes they come in a Tape and Reel packaging, similar to a reel of tape with a set amount per reel, and sometime they come lose in a bag. This formula will help me understand if they come on a tape and reel, then I must always calculate the multiples based on how they come packaged. If they come lose in a bag ( Bulk ), then I can calculate them by counting 1,2,3. I need to understand if I do not have stock Qty, then how many do I need to buy. If the Profile is set to "S" then I know that I can always order by the package size. If the profile is "N", and I do not have enough stock, then I have to order by the MOQ.MOQ = Minimum Order Qty. the amount required to order
MPQ = Multiple Package Qty. the size of the packaging container

Profile ( S = Always have in stock / N = not always in stock )

This is my current Code, it is not working correctly.
Code:
` =IF(B2>=ROUND((100/C2/E2)*E2,0),IF(AND(OR(A2="S",A2="N"),F2="TR"),ROUND((100/C2/E2)*E2,0),IF(AND(A2="S",F2="TR"),ROUND(100/C2,0),IF(AND(A2="N",F2="TR"),D2,""))),ROUND(100/C2,0))`
 Profile Stock Qty Price MOQ MPQ Packaging MOQ Formula S 456 1.00 463 1 BLK S 10 2.00 71 1 BLK N 50 1.50 100 100 TR S 10 1.00 2 1 BLK N 10000 25.00 56 1 BLK N 10000 .001 9000 3000 TR N 10.00 3 1 BLK S .01 500 400 TR S 100 .01 100 500 TR  Reply With Quote

2. Based on your pseudocode, try this formula:

=IF(A2="S",IF(F2="TR",ROUND(100/C2/E2,0)*E2,100/C2),IF(F2="TR",IF(B2>=ROUND(100/C2/E2,0)*E2,ROUND(100/C2/E2,0)*E2,D2),IF(B2>=ROUND(100/C2/E2,0)*E2,100/C2,D2)))

 Profile Stock Qty Price MOQ MPQ Packaging MOQ Formula S 456 1 463 1 BLK 100 S 10 2 71 1 BLK 50 N 50 1.5 100 100 TR 100 S 10 1 2 1 BLK 100 N 10000 25 56 1 BLK 4 N 10000 0.001 9000 3000 TR 9000 N 10 3 1 BLK 3 S 0.01 500 400 TR 10000 S 100 0.01 100 500 TR 10000  Reply With Quote

3. Thank you! This works great, except for row 7 and 8. I need to MOQ to always equal \$100 or more. Using row 7 for an example: 9000 x \$.001 = \$9.00. I would need the MOQ Formula to actually calculate a result of 102,000 instead.  Reply With Quote

4. Are you sure you mean lines 7 and 8?

Also, not sure how you get 102,000... can you elaborate on the logic behind that result?  Reply With Quote

5. I was able to elevate the formula to where I need it, but I am not able to understand why it will return a value of "0" when the MOQ/MPQ is over 100 pcs? I believe the Round is what is causing this? There are also a few other scenarios I am trying to include into the formula. Any suggestions?

Code:
` =(IF(G2="S - Stock",IF(H2="TR",ROUND(100/A2/E2,0)*E2,ROUND(100/A2,0)),IF(H2="TR",IF(B2>=ROUND(100/A2/E2,0)*E2,ROUND(100/A2/E2,0)*E2,IF(B2>=ROUND(100/A2/E2,0)*E2,IF(D2="",C2,D2),ROUND(100/A2/E2,0)*E2)),IF(B2>=ROUND(100/A2/E2,0)*E2,ROUND(100/A2,0),IF(B2>=ROUND(100/A2/E2,0)*E2,IF(D2="",C2,D2),ROUND(100/A2/E2,0)*E2)))))`
Price Stock MOQ Approved MOQ MPQ Approved MPQ Stock Profile Package Formula Comments
1.1 1000 1000 N - Non TR 0
 why is MOQ "0"? If I lower the MOQ/MPQ to 100 then it will work???
1.0 1500 500 N - Non TR 0
 why is MOQ "0" If I lower the MOQ/MPQ to 100 then it will work???
1.4824 3000 3000 3000 S - Stock TR 0
 why is MOQ "0" If I lower the MOQ/MPQ to 100 then it will work???
.0429 3000 3000 S - Stock TR 0
 why is MOQ "0" If I lower the MOQ/MPQ to 100 then it will work???
53.49 1 1 N - Non BLK 2
 correct
23.54 2646 126 122 N - Non BLK 4
 How to make Approved MPQ supersede?
17.21 47 1 1 S - Stock BLK 6 correct
.1587 6367 7200 100 S - Stock BLK 630 correct
.04 #VALUE!
 How to make Approved MOQ/MPQ supercede? And one step further to still work without Stock Profile
.052 #VALUE!
 How to make Approved MOQ/MPQ supercede? And one step further to still work without Stock Profile  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•