+ Reply to Thread
Results 1 to 6 of 6

Help with an inventory spreadsheet

  1. #1
    Registered User
    Join Date
    11-01-2012
    Location
    Gunnison, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    2

    Help with an inventory spreadsheet

    I'm having difficulty limiting a value for the number of pieces to order.

    The formula I'm using to calculate the third column is =+IF(C5=D5>0,C5,"0") The problem I'm having is seen in row 4. The number sold is 7 and the adjustment is -5. I'm not sure how to have the "if true" statement become another IF function. Ideally, I'd like the number to order for row 4 to show as 2 in this case, or 0 if the number sold is fewer that the adjust number.

    Thanks for your help!

    Matt

    Number sold Adjust Number to order

    1 -4 0
    2 -28 0
    0 -13 0
    7 -5 7
    2 19 2
    4 12 4
    0 14 0
    1 12 1
    1 8 1
    1 8 1

  2. #2
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Help with an inventory spreadsheet

    Hi Big,

    First off, your condition (C5=D5>0) is incorrect. This should produce a true or false result. In other words, only one operator.

    What you are looking for is a Nested IF statement, but I think the AND formula will work better for you.

    =IF(AND(C5=D5,D5>0),C5,0)

    Is this correct?

  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Help with an inventory spreadsheet

    Bigtroutcather,

    Welcome to the forum!
    I'm not 100% certain, but if I understand correctly, this formula should work for you:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Help with an inventory spreadsheet

    Oh my mistake, your condition (C5=D5>0) should have read C5+D5>0, right?

    Then ignore my formula and you should go ahead with a nested IF...

    =IF(C5+D5>0,IF(condition,true,false),0)

    Note: no need to quote 0s ("0"), unless you want it to apprear as text

    re-post: very elegant, tiger... nice work!

  5. #5
    Registered User
    Join Date
    11-01-2012
    Location
    Gunnison, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Help with an inventory spreadsheet

    djapigo,

    You're correct. The = should have been a +. Sorry for the typo.

    tigeravatar,

    The max formula works great. Never seen it before. I guess I have a little reading to do.

    Thank you both for your help!

    Matt

  6. #6
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Help with an inventory spreadsheet

    You're very welcome

    If that takes care of your need, please mark this thread as solved.
    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:
    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1