How to get accumulated result on a cell?

mrr1671 · May 6, 2003

Hi to all, this is what I am looking for and don't know if can be done, we have the data in 3 cells, one is for the initial value, second is for the qty. to add to the first one and the third is for the qty. to substract if is needed. In a fourth cell we want to have the result of the three cells.
Initially if the 2nd and 3rd are zero the value on the 4th will be the same as the 1st, if we change the value on the 2nd, the value on the 4th will change adding this value, if change again now the value on the 4th will change from it's last value by that amount changed on 2nd or 3rd.
In other words the 4th cell is like a memory and always will have the last result from the last inputs on the addition or substracting cells.

IRstuff · May 6, 2003

What you seem to be asking at the beginning of the question and the end seem to be inconsistent. You seem to be asking whether D1 can be set to A1+B1-C1, which is core functionality of Excel, so I'm guessing that you want something else.

If you're asking whether Excel can remember a previous result, the answer is possibly, depending on how much work you want to put in. The simplest approach would be to add a 5th column=Now(), which returns the current time. Copy D1 and E1 into F1 and G1 using Paste Special|Values, which simply copies the numerical values of the equations into new cells for later reference. The more complicated approach would be to automate that process using Visual Basic.

TTFN

joerd · May 8, 2003

What I would usually do is just write the following equation in D2 ( =D1+B2-C2 ), and input every new entry in a new row in the B or C column. In this way you can also see a "log" of all your entries in the spreadsheet:

Code:

    A   B   C    D
1  100  10  20   90
2       10   0  100
3        0  50   50
etc.

Another approach can be to set Iteration on with Maximum Iterations = 1 (go to Tools | Options in the menu)
You would have to enter the initial value in column B, and the equation in D1 will be =D1+B1-C1 (so it points to itself). Every time you change something in B1 or C1, the value in D1 is updated. This is a bit tricky, since once you make a mistake, there is no easy correction possible. Even if you Undo your last entry, the value in D1 will keep on being calculated "forward"

Regards,

Joerd

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

How to get accumulated result on a cell?

mrr1671

Mechanical

Recommended for you

IRstuff

Aerospace

joerd

Chemical

Similar threads

Part and Inventory Search

Sponsor