A running balance problem for several products

1. 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. 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. 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. 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.

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

Users Browsing this Thread

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

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