+ Reply to Thread
Results 1 to 12 of 12

Trying to Write a formula to calculate available inventory

  1. #1
    Registered User
    Join Date
    12-17-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    62

    Trying to Write a formula to calculate available inventory

    Need help to write a formula in cell K9 to add the highest value without causing errors.

    We have a current QTY of 30 cases on the floor. We have replenishment orders that will add additional inventory, but have also reserved product for future orders. We can still add an order, but for how much? I figured this is 20, but need a formula to determine easily.

    Can someone help me please?

    Thanks
    Adam
    Attached Files Attached Files
    Last edited by AliGW; 09-19-2019 at 12:55 AM. Reason: Irrelevant section of title removed: this is a HELP forum!!!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,813

    Re: Trying to Write a formula to calculate available inventory

    Maybe this:

    =SUMIF($2:$2,"Replenish",$9:$9)+SUMIF($6:$6,"Current",$9:$9)-SUMIF($2:$2,"Reserved",$9:$9)
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    12-17-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    62

    Re: Trying to Write a formula to calculate available inventory

    Mr. Kennedy,

    You are a gentleman and a scholar. Thank you!

    Best,
    Adam

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,813

    Re: Trying to Write a formula to calculate available inventory

    And you're not a bad chap, either!!

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  5. #5
    Registered User
    Join Date
    12-17-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    62

    Re: Trying to Write a formula to calculate available inventory

    Thank you, Sir!

    So.. it seems this didn't quite work as we'd hoped... once I removed a future reserve, an error popped up.

    Still trying to work this one out! If you have any additional thoughts, they'd be more than welcomed!

    Best,
    Adam
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,813

    Re: Trying to Write a formula to calculate available inventory

    Sh1t happens. I'll take a look.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,813

    Re: Trying to Write a formula to calculate available inventory

    Maybe this in L9:

    =E9-SUMPRODUCT((ISNUMBER(F6:K6))*F9:K9)

  8. #8
    Registered User
    Join Date
    12-17-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    62

    Re: Trying to Write a formula to calculate available inventory

    Unfortunately cant change any of the formulas in the row..

    L9 calculates the inventory at a future date. So if we have a new order, we'd only be able book 20 (in K9) without triggering errors (causing future inventory to be <0)

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,813

    Re: Trying to Write a formula to calculate available inventory

    Going out for dinner now. How can I get it to work, without changing formulae.... either current or new??

  10. #10
    Registered User
    Join Date
    12-17-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    62

    Re: Trying to Write a formula to calculate available inventory

    Is there some sort of formula that works similarly to a what-if? Meaning, something that can hit a MAX number without causing "ERROR" in a row?

  11. #11
    Registered User
    Join Date
    12-17-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    62

    Re: Trying to Write a formula to calculate available inventory

    Just saw your note. I just asked a coworker to take a look as well.. It's not an easy one.

    Bc of the way this all works, the only cell that can be altered (besides the hard-coded) is K9... total pain!

    I think my report is going to have to be more complicated than originally anticipated.. Such is life. Can't thank you enough, Glenn.

  12. #12
    Registered User
    Join Date
    12-17-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    62

    Re: Trying to Write a formula to calculate available inventory

    Finally figured it out..

    See attached FYI.

    Simple as a MIN formula.. Just needed to rack my brain a bit.

    Thank you VERY MUCH!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Request: Help to Write Formula to Calculate Available Inventory (excel attached)
    By OilAndGasMan1984 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-18-2019, 01:26 PM
  2. Formula Needed - Inventory Tracking
    By Rterp44 in forum Excel General
    Replies: 1
    Last Post: 03-19-2019, 04:02 AM
  3. Simple Inventory formula needed (Again)
    By tta.akmal in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-17-2018, 02:58 AM
  4. [SOLVED] Simple Inventory formula needed
    By tta.akmal in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-13-2018, 02:58 AM
  5. How to write the Formula for calculation day of inventory when month has change
    By Akatecho in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-20-2014, 02:28 PM
  6. Inventory Age Formula Needed for FIFO Inventory
    By SWeisser in forum Excel General
    Replies: 0
    Last Post: 07-20-2012, 02:44 PM
  7. Replies: 1
    Last Post: 01-19-2008, 06:18 PM

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