Results 1 to 5 of 5

Thread: IF Statement based on the IF Statement result

  1. #1

    IF Statement based on the IF Statement result



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

    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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    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


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

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    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?


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

Posting Permissions

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