+ Reply to Thread
Results 1 to 6 of 6

A running balance problem for several products

  1. #1
    Registered User
    Join Date
    09-21-2011
    Location
    Boston, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    A running balance problem for several products

    Hi

    I have a four column table (say A,B,C,D) comprising a ‘product description’, the ‘quantity ordered’ (integer value) ‘date ordered’, and quantity remaining.

    Product Quantity Date Remaining

    Product 4 5 04/01/13
    Product 5 8 10/01/13
    Product 2 5 23/01/13
    Product 4 10 06/02/13
    etc


    I want column (D) to return the number of this particular product remaining after the order has been made (i.e. value entered into column B)

    At the beginning of the year we tell the suppliers how many we items of a particular product we intend to order (a ‘call-off ‘system) and the total then ‘runs down’ during the course of the year.

    There are only five products (let’s say Product 1, Product 2,…. Product 5) so the user selects the product from a drop-down list attached to each cell in column A (using Data Validation | Allow | List). This reference list (Product 1 .. Product 5) is in $W$10:$W$14. The adjacent column (X10:X14) has the ‘starting number ‘ (for each product) at the beginning of the year:

    Product 1 19
    Product 2 45
    Product 3 25
    Product 4 58
    Product 5 89

    It is easy to calculate the number remaining for a particular product the first time it is ordered, but I can’t work out how to deal with subsequent orders of the same product.

    In cell D1 at present I have:

    = VLOOKUP(A1,$W$10:$X$14,2,FALSE)-B1) … then copy down column D.

    … but this obviously doesn’t work as the ‘reference table’ is not updated after each order.

    Many thanks for your help.

    Phil

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: A running balance problem for several products

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Valrico, FL USA
    MS-Off Ver
    Excel 2016
    Posts
    358

    Re: A running balance problem for several products

    Try pasting the formula below into cell D1 then copy down:

    =SUMPRODUCT(($W$10:$W$14=A1)*($X$10:$X$14))-SUMPRODUCT(($A$1:A1=A1)*($B$1:B1))

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: A running balance problem for several products

    Here is a simple sample of inventory tracking that you may be able to adapt to your situation. It is a bakery but the principles are much the same.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    09-21-2011
    Location
    Boston, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: A running balance problem for several products

    Brilliant! That works. I haven't used SUMPRODUCT much in the past but I it is obviously a very useful function.
    I will break down and analyse your suggestion to make sure I fully understand it, so can apply the construct to other situations.

    Many thanks for your help. Much appreciated.

    Phil

  6. #6
    Registered User
    Join Date
    09-21-2011
    Location
    Boston, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: A running balance problem for several products

    Thanks Ron

    The previous suggestion solved the problem but I will certainly take a look at the bakery application. There is always more than one way of solving these problems.

    Regards, Phil

+ 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] Running Balance shown problem
    By jawwad321 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-26-2013, 02:17 PM
  2. Running balance
    By Greydon in forum Excel General
    Replies: 12
    Last Post: 04-28-2010, 12:49 PM
  3. Running Balance problem
    By Robotacha2010 in forum Excel General
    Replies: 6
    Last Post: 02-06-2010, 09:51 AM
  4. Running balance
    By Steve-B in forum Excel General
    Replies: 10
    Last Post: 07-21-2009, 04:04 AM
  5. [SOLVED] rent received/balance owed/running balance spreadsheet
    By Quickbooks dummy in forum Excel General
    Replies: 1
    Last Post: 01-02-2006, 03:40 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