Results 1 to 5 of 5

Thread: *Round* and *IF*

  1. #1

    *Round* and *IF*



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Please post a sample sheet so we can work on it. Thanks

  3. #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. #4
    You need to explain what you are trying to do, the logic behind each value.

  5. #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
  •