How can I avoid circular reference AND extra input

[maintchief]

My wife is a hotel breakfast hostess. Her boss wants her to keep a weekly
inventory of breakfast food items. In trying to EXCEL it I run into a
problem.
Three columns: Previous Inventory, Additions/Subtractions(+/-), New Inventory.

If I add +/- to Previous Inventory to get New Inventory I get a circular
reference problem.
To avoid this I figure she will have to input Previous Inventory (manually)
from New Inventory and then input +/- to get a 'new' New Inventory. Is there
a way to avoid the double manual input and avoid the circular reference
problem?

 

[bpeltzer]

You shouldn't have a cirular reference. If the table is in columns A:C with
the header in row 1 and the first data in row 2 then:
A2 should have the starting inventory, B2 the first change (both are fixed
values) and C2 should have the calculated new inventory: =A2+B2.
In row three, the 'previous inventory' should be the prior row's 'new
inventory.' So in A3, =C2. B3 has the next update. And C3 is calculated
again as previous inventory + change: =A3+B3.
Now you should be able to copy the formulas from row 3 down to each new row,
only filling in the +/- in column B of each row, and with no circular
references.

 

[maintchief]

I gave it a try and as you already know it worked. However with 40
inventoried items this growing for each item method becomes rather unwieldy.
Can you think of a way to keep the calculations on one line for each item?

 

[bpeltzer]

You could add another column to indicate which product you're dealing with.
So now your four columns are Product, Previous Inventory, +/-, and New
Inventory.
The previous inventory calculation, starting in B2 could be
=sumif(a$1:a1,a2,c$1:c1). D2 would be =B2+C2. Only change now is that you'd
need to 'prime' this with an initial entry to each item to 'add' the starting
inventory; the sumif function calculates prior inventory by looking backward
and totalling all the +/-'s for that product.