IF Statement based on the IF Statement result

[AVT_SMM]

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]

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.

 =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

[NBVC]

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

[AVT_SMM]

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.

[NBVC]

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?

[AVT_SMM]

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?

 =(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