Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have one sheet for products purchased and another one that sold. I want to make a pivot table from two sheet to get current inventory for each product. But options are only sum, average etc there is no subtraction. How could I do that? I am using Excel 2007. Thanks

share|improve this question

The simplest would be to store the number sold as negative numbers.

If they're currently all positive numbers, you can easily change them to negative by typing -1 in an empty cell, copying it, and doing a Paste Special - Multiply on the number sold column.

share|improve this answer
THanks but not looking that. – Gok Demir Feb 5 '10 at 15:56
Eh? (Do you mean "this won't work for me"? If so, why not?) – Martha Feb 5 '10 at 16:22

Sounds like your problem is that the data is in two places. Possible solutions: a) Make 2 Pivot Tables for the two chunks of data. Use GetPivotData formulas to extract the values for the matching items (eg by product code) and subtract them.

b) Carry some of the data across from one sheet to the other using eg VLOOKUP to fetch the starting inventory figure across to your sales table, but only for the first occurence of a product being sold. Include this extra column in your sales pivot and add a calculated field to subtract them.

I prefer (a) but you would have to do some prep to create a list of unique product codes (paste sales + inventory together and remove duplicates then sort) so you can point the GPD functions at those cells.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .