Calculating totals

  • Thread starter Thread starter =?ISO-8859-1?Q?L=FCpher_Cypher?=
  • Start date Start date
?

=?ISO-8859-1?Q?L=FCpher_Cypher?=

Say, I need to calculate a total given credits and debits. Suppose
credits are in column A and debits are in column B, and the balance is
in column C.
Is there a way to do something like

[in C:]
=SUM(A1:{current})-SUM(B1:{current})

So that C{current} has the balance?
I'm kond of new to excel but what the hell :)
I need it to select a range from a explicidly specified column to the
current column.. I'm pretty sure MS did it, but I'm new to Excel.

Thanks
Lüph
 
Assuming the data in cols A and B starts in row1 down
and that debits (in col B) are entered *without* a minus sign

Try in say, C1:

=SUM(OFFSET($A$1,,,1000),-OFFSET($B$1,,,1000))

Adjust the "1000" in the formula
to suit the "max" likely number of rows
which would be ever be encountered

(You can adjust up to a max number of : 65535)

And if the debits (in col B) are entered *with* a minus sign
just remove the "-" from the ".. -OFFSET($B$1,,,1000).. viz.:

Put instead in C1:

=SUM(OFFSET($A$1,,,1000),OFFSET($B$1,,,1000))
 
Sorry, scratch the earlier post, forgot to mention
that the formula has to be array-entered ..

Here's the revised post ..
--------
Assuming the data in cols A and B starts in row1 down
and that debits (in col B) are entered *without* a minus sign

Try in say, C1:

=SUM(OFFSET($A$1,,,1000),-OFFSET($B$1,,,1000))

Array-enter the formula:
i.e. Press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Excel will wrap curly braces { } around the formula, viz.:
{=SUM(OFFSET($A$1,,,1000),-OFFSET($B$1,,,1000))}

(don't key-in the curly braces yourself)

Adjust the "1000" in the formula
to suit the "max" likely number of rows
which would be ever be encountered

(You can adjust up to a max number of : 65535)

And if the debits (in col B) are entered *with* a minus sign
just remove the "-" from the ".. -OFFSET($B$1,,,1000).. viz.:

Put instead in C1:
=SUM(OFFSET($A$1,,,1000),OFFSET($B$1,,,1000))

(Remember to "array-enter" the formula whenever you edit it)
 
It is not clear exactly what you are asking for.

Max has noted that =SUM(A:A)-SUM(B:B) will give the balance based on all
the values in A and B. If you only want it to appear on the "current"
row, then you could wrap it in an IF() formula that identifies the
current row.

For a running balance in column C, use =SUM(A$1:A2)-SUM(B$1:B2) in row 2
and copy it down.

Alternately, =C1+A2-B2 copied down would give you a running balance more
efficiently for a large number of rows. With a ledger sheet where some
automated transactions can occur (interest, deposits from other sources,
etc.), you might have to insert transactions. The danger in not
re-summing the entire column is that inserted entries may be skiped
unless you always recopy the formula over the column after each insert.
=OFFSET(C2,-1,0)+A2-B2 copied down would adjust itself to inserted
transactions.

Jerry
 
Back
Top