Using excel like a check register
 

i am using excel to evaluate and project our budget. i have it set up like a
check register, with "withdrawal" "debit" and "total" columns. up until now,
i have been creating individual formulas for the total column, depending on
whether i have a withdrawal on that line or a debit (formula ex1: =E2-C3)
(formula ex2: =E2+D3).
anytime i add or delete a row in my budget, i have to redo each individual
formula to fit the new items designation (either telling excel to add it or
subtract it).
is there a way i can put the same formula in the entire "total" column that
tells excel to "subtract if there is a number in column C (withdrawals) or
add if there is a number in column D (debits)" ?
thanks!
leslie

Using excel like a check register
 

Try this, entered into cell E3:

=OFFSET(E3,-1,0)+OFFSET(E3,0,-1)-OFFSET(E3,0,-2)

This will take the value of the cell 1 row above E3, then add the value from
the cell 2 columns to the left of E3, then subtract the value from the column
1 column to the left of E3.

This can then be copied down your entire Total row. It will not matter if
you insert or delete rows, since the only cell being referenced is the cell
the formula is in.

HTH
Elkar


"leslie" wrote:

i am using excel to evaluate and project our budget. i have it set up like a
check register, with "withdrawal" "debit" and "total" columns. up until now,
i have been creating individual formulas for the total column, depending on
whether i have a withdrawal on that line or a debit (formula ex1: =E2-C3)
(formula ex2: =E2+D3).
anytime i add or delete a row in my budget, i have to redo each individual
formula to fit the new items designation (either telling excel to add it or
subtract it).
is there a way i can put the same formula in the entire "total" column that
tells excel to "subtract if there is a number in column C (withdrawals) or
add if there is a number in column D (debits)" ?
thanks!
leslie