1. ## *Round* and *IF*

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 Type 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 How to make Approved MOQ/MPQ supersede? And one step further to still work without Stock Profile .052 How to make Approved MOQ/MPQ supersede? And one step further to still work without Stock Profile

2. Please post a sample sheet so we can work on it. Thanks

3. 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?

4. You need to explain what you are trying to do, the logic behind each value.

5. 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)

#### Posting Permissions

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