make a single cell always equal zero

[Brad Best]

the problem I am facing is a I think a simple one and based on the posted
question and answers I have been reading I'm very sure much simpler than I'm
making it.

I would like my budget spread sheet to auto balance itself to automate the
entry of pending payments and outgoing bills. I want the "Totals" cell to
always equal zero, taking any overage or underage from a single cell:
A B C
11Savings $121.08 $121.08
12 $0.00 Balance
13Total $1,646.64 $0.00
C3 I want to always to equal 0 drawing + or - from B1
Can this be done: any help would be greatly appreciated

Brad Best

 

[Pete_UK]

Not sure I fully understand, but perhaps you mean:

=-B1

Hope this helps.

Pete

 

[Spiky]

the problem I am facing is a I think a simple one and based on the posted
question and answers I have been reading I'm very sure much simpler than I'm
making it.

I would like my budget spread sheet to auto balance itself to automate the
entry of pending payments and outgoing bills. I want the "Totals" cell to
always equal zero, taking any overage or underage from a single cell:
A B C
11Savings $121.08 $121.08
12 $0.00 Balance
13Total $1,646.64 $0.00
C3 I want to always to equal 0 drawing + or - from B1
Can this be done: any help would be greatly appreciated

Brad Best

Auto-balancing is always a little frightening to me, leaves a LOT of
room for the error of forgetfulness. But you usually want to put the
auto-balance cell near the Total cell, they can't be the same or it
won't look proper. You might as well just type "0" into the cell in
that case.

It's not quite clear what all your cells are since columns don't copy
well and I'm not exactly sure what you're doing. But maybe C3 is
=SUM(B1:B2)-SUM(C1:C2)
And then the Balance Total is in D3: =SUM(B1:B3)-SUM(C1:C3)

 

[Spiky]

Sorry, forgot definitions:

C3 is then the balancing amount.
D3 is the total you want that should equal zero.

 

[Brad Best]

not sure how relevant his is however but c3 is a weekly total that references
the previous weeks total and the current weeks totals as well and of course
is the cell I want to =0, the effected cell needs to be b1 as any shortage
for the week in outgoing bills need to come from the savings entry,
transversely all excess funds need to go into b1 hence b1 needs to adjust
itself to ensure that c1 remains =0

 

[Spiky]

not sure how relevant his is however but c3 is a weekly total that references
the previous weeks total and the current weeks totals as well and of course
is the cell I want to =0, the effected cell needs to be b1 as any shortage
for the week in outgoing bills need to come from the savings entry,
transversely all excess funds need to go into b1 hence b1 needs to adjust
itself to ensure that c1 remains =0

Well, it's certainly relevant. But I'm not sure it is enough. Where is
the previous week total? Where is the current week total? What is the
actual formula in C3?

If I'm understanding, you want to have B1 equal the inverse of all the
other cells being added in C3, that will always make C3=0. So I guess
you have to look at the formula in C3 and reverse it in B1, more or
less. The only issue is to make sure you don't cause a circular
reference.

 

[Brad Best]

Well, it's certainly relevant. But I'm not sure it is enough. Where is
the previous week total? Where is the current week total? What is the
actual formula in C3?

If I'm understanding, you want to have B1 equal the inverse of all the
other cells being added in C3, that will always make C3=0. So I guess
you have to look at the formula in C3 and reverse it in B1, more or
less. The only issue is to make sure you don't cause a circular
reference.

the circular reference is a problem and again why im asking for help

so C3 actually references another weeks + incoming - outgoing and is
specific to each week. now b1 at current is a simple input cell which can be
relocated to accommodate a formula as im sure that will need to happen
anyway. im not even sure if im helping at this point or making things more
complicated.

 

[Spiky]

the circular reference is a problem and again why im asking for help

so C3 actually references another weeks + incoming - outgoing and is
specific to each week. now b1 at current is a simple input cell which can be
relocated to accommodate a formula as im sure that will need to happen
anyway. im not even sure if im helping at this point or making things more
complicated.

Well, you aren't exactly giving any more info. So I'd say you're
neither helping nor making it more complicated.

Sounds like you want C3 to be: PriorBalance + Incoming - Outgoing +
AdjustCell

So you need that AdjustCell to have this formula: Outgoing - Incoming
- PriorBalance