Software

Question

• Creator
  Topic
• July 21, 2007 at 11:04 am #2225155
  

  How to set up a formula in Excel for calculating inventory

  Locked

  by l_rmorin · about 16 years, 9 months ago

  I want Excel to calculate inventory on items and keep a running total as items are used.
  For example I have 309 cases of Beef patties at the beginning of the week and 10 cases are used on monday and the another 5 are used on wednesday and another 5 ae used on thursday I want to show a running balance as they are used. I tried to set up a formula to do this and the end result always showed value instead of of the balance.

  This conversation is currently closed to new comments.
• Creator
  Topic

All Answers

• Author
  Replies
• • July 21, 2007 at 11:04 am #2621878
    

    Clarifications

    by l_rmorin · about 16 years, 9 months ago

    In reply to How to set up a formula in Excel for calculating inventory

    Clarifications

  • July 21, 2007 at 1:15 pm #2621852
    

    Inventory in Excel

    by stevehab · about 16 years, 9 months ago

    In reply to How to set up a formula in Excel for calculating inventory

    If your requirement is to show remaining inventory, and you have an reliable input on what has been removed from inventory, then there should be several ways to get where you want to be. Since I have no format to look at, this suggestion may fail to meet your needs.

    Once way would be to have a tab just for inventory. List dates in column A going down to show activity. Label what an entry is for , generally related to a meaning for “used”, “purchased”, or maybe adjustments for missing or spoiled. Begin with a row for beginning inventory. So, now you may have a list of 309, -10, -5, … the sum of which is your current “book” inventory. My preference for this may be to leave a frew rows at the top, lock the rows so I can see my column headers and us the SUM function on the column with inventory numbers. This way you can refer to a fixed location if you are using this in another tab.

    Hope this helps or provides a spark that helps you.

    • July 22, 2007 at 6:38 am #2621735
      

      What I need

      by l_rmorin · about 16 years, 9 months ago

      In reply to Inventory in Excel

      I can’t show you exactly what I need as space here is limited.
      If I could e-mail you the worksheet I have set up then You would see what I am trying to do. What is below is not showing what I am trying to accomplish.

      Items Beginnig Amount Monday Tuesday Wednesday Thursday Friday Ending Amount
      Beginning Inventory 30 10 5 5 #VALUE!
      Beef Pattlies
      French Fries

  • July 21, 2007 at 1:49 pm #2621840
    

    Running total?

    by nicknielsen · about 16 years, 9 months ago

    In reply to How to set up a formula in Excel for calculating inventory

    Do you want that total be the source for the next use? In other words, do you want to subtract the 10 cases on Monday to get a balance of 299, then open the program on Wednesday and see that 299 balance as your starting point? I don’t know how you can do that in Excel without some serious VBA.

    A simple Excel solution is:

    Top row: starting inventory (manually entered)
    Next row: current balance (formula = starting inventory – sum(removals)
    Subsequent rows: individual removal entries.

    Or you could try a pre-built database solution such as this [free] one: http://www.freewarefiles.com/program_8_88_12345.html

    • July 22, 2007 at 6:49 am #2621728
      

      Follow up on your reply

      by l_rmorin · about 16 years, 9 months ago

      In reply to Running total?

      I start with 30 cases of an item and deduct 10 cases on monday which leaves me 20 cases for the rest of the week then on another day another day 5 cases are removed (used) which leaves me 15 cases for further use.

      I cant post the worksheet here as it soes not post correctly.

    • July 22, 2007 at 6:53 am #2621724
      

      Tried what you suggested and there in lies the problelm.

      by l_rmorin · about 16 years, 9 months ago

      In reply to Running total?

      Top row: starting inventory (manually entered)
      Next row: current balance (formula = starting inventory – sum(removals)
      Subsequent rows: individual removal entries.

      This gives me #Value instead of a resulting figure.

      • July 22, 2007 at 7:17 am #2621716
        

        Formulas

        by nicknielsen · about 16 years, 9 months ago

        In reply to Tried what you suggested and there in lies the problelm.

        The #VALUE means there is an error in the formula. Did you use “removals” as the variable without defining the range? This could cause the problem.

        Build the formuala like this:
        – click on the cell where you want the results displayed
        – type [b]=[/b]
        – click on the cell containing the starting balance
        – type [b]- sum([/b]
        – click and drag from the first cell containing removal data to the last cell for that item.
        – press Enter
        – you should end up with a formula that looks something like this: [b]=B6-SUM(C6:G6)[/b]

        I can also send you a sample file if you like.

        Edit: runaway [b]bold[/b]

      • July 23, 2007 at 6:43 am #2622052
        

        Thanks

        by l_rmorin · about 16 years, 9 months ago

        In reply to Formulas

        I had not tried that. I will try it and I am certain that it will work.

        I was really getting frustrated with this. Thanks again.

• Author
  Replies