0

I have data that corresponds to "usage" of a product. I can manually update the value as it increases and save the previous values somewhere else. However, I would like to have the previous value automatically saved as a part of a list in the same cell when it is updated. This would allow me to view how the amounts have changed and what is their effect on the end result without much effort.

Is there a viable way to do this, or should I just create lists manually for each set of old and new values?

I hope this was clear enough.

I am an excel scrub with no real training. There might be an even better way to do this than my idea.

enter image description here

Anything is welcome!

Improve this question
5
  • Please upload a screenshot of your spreadsheet
    – SQLTemp
    Commented Feb 5, 2020 at 10:21
  • 1
    Does putting '=F2' in cell C3, then '=C2-E3' in cell F3 do the job ?
    – p._phidot_
    Commented Feb 5, 2020 at 19:16
  • @p._phidot_ Sadly, I need each line to be counted separately, since those liquids come from different batches. All I need is to display the amounts in usage as a list (so, as it increases, I can pick from 200, 400, 550, etc. liters to see how many times it was refilled)
    – Newbael Scrubolous
    Commented Feb 7, 2020 at 8:57
  • if each line is separately calculated, the value in F3 should be 800 right ?
    – p._phidot_
    Commented Feb 9, 2020 at 8:00
  • You are right, I messed up the equation in the second line.
    – Newbael Scrubolous
    Commented Feb 10, 2020 at 11:42

1 Answer 1

Reset to default
2

You can do it with a slight change in your table, 2 different formulas, and an understanding of anchoring.

Table changes: you don't need column C and column E become really stock/usage. So, if you are adding stock then it is positive, but usage is negative.

enter image description here

Formula in Column D (starting with D2 and drag down): =MAXIFS($A$1:A1,$B$1:B1,B2) This looks for the largest date based on what test name is in that row. Notice the formula ends one row above the formula row. The part that says $A$1 is known as an anchored cell. It keeps that part of the formula constant when you drag the formula down.

the Formula in column F (starting with F2 and drag down) is: =SUMIFS($E$1:E2,$B$1:B2,B2). This adds all stocking and usage from the above cells (including the row the formula is on) for that particular name (item).

If you add filtering, then the last row will always be remaining stock for a particular item.

There are other ways to solve your problem, I just solved based on how the data is presented.

Improve this answer
3
  • Thanks! It is a bit different from what I wanted, but it does solve the problem. If I split the anchored cell and the stock changes to different sheets (to keep delivery and usage separately), would that make adding new anchors easier, or do you suggest keeping everything in one sheet?
    – Newbael Scrubolous
    Commented Feb 10, 2020 at 11:40
  • For me, I would just filter the positive items in the "usage" column, that gives you replenishing of the stock (I think that is what you mean by delivery), and filter negative numbers to get usage.
    – gns100
    Commented Feb 10, 2020 at 19:14
  • An example of alternative is to just keep column A, B and E. Then pivot the table...
    – gns100
    Commented Feb 10, 2020 at 19:15

You must log in to answer this question.

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