*Round* and *IF*

A

AVT_SMM

New member
Joined
Mar 9, 2015
Messages
7
Reaction score
0
Points
0
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)))))
PriceStockMOQApproved MOQMPQApproved MPQStock ProfilePackage TypeFormulaComments
1.110001000N - NonTR0why is MOQ "0"? If I lower the MOQ/MPQ to 100 then it will work???
1.01500500N - NonTR0why is MOQ "0"? If I lower the MOQ/MPQ to 100 then it will work???
1.4824300030003000S - StockTR0why is MOQ "0"? If I lower the MOQ/MPQ to 100 then it will work???
.042930003000S - StockTR0why is MOQ "0"? If I lower the MOQ/MPQ to 100 then it will work???
53.4911N - NonBLK2correct
23.542646126122N - NonBLK4How to make Approved MPQ supersede?
17.214711S - StockBLK6correct
.158763677200100S - StockBLK630correct
.04How to make Approved MOQ/MPQ supersede? And one step further to still work without Stock Profile
.052How to make Approved MOQ/MPQ supersede? And one step further to still work without Stock Profile
 
Please post a sample sheet so we can work on it. Thanks
 
Unfortunately, It will not allow me to post an attachment. The table in my post and the formula in the post is the exact data i'm working from if that helps?
 
You need to explain what you are trying to do, the logic behind each value.
 
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, then how many do I need to buy. If the Profile is set to "S-stock" then I know that I can always order by the package size. If the profile is "N-non", 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-Stock = Always have in stock / N-non = not always in stock )
If "profile" = S
Is “Packaging” = TR
Y - $100/Price/MPQ*MPQ
N- $100/Price

If “profile” = N
Is “Package” = TR
Y - $100/Price/MPQ*MPQ
Is "Stock Qty" >= above statement?
Y- $100/Price/MPQ*MPQ
N- MOQ(approved MOQ to supersede)
N – $100/Price
Is "Stock Qty" >= above statement?
Y- $100/Price
N- MOQ(Approved MOQ to supersede)
Click to expand...
 
You must log in or register to reply here.
Back
Top