How can I avoid circular reference AND extra input

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?

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" wrote:

> 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?

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" wrote:

> 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" wrote:
>
> > 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?

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.

"maintchief" wrote:

> 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?