Balance Column

D

Debra Dalgleish

Use the following formula in cell K2, and copy down to the last row of data:

=IF(ISNUMBER(K1),K1,0)+J2
Is there a function where i can find the moving balance?

For example

i have DR and CR.

i can do Balance = DR cell + Cr Cell + Previose Balance


BUT whenI DO A SORT on the data, the column that contains the balance
formula will now not work

i attact link to pic of spreadsheet to explain things better


[image: http://member.telpacific.com.au/soke/general/msexcel1.jpg]

and after i sort the data....

[image: http://member.telpacific.com.au/soke/general/msexcel2.jpg]
 
D

David McRitchie

Hi Debra,
That doesn't solve the problem it is exactly what he was doing
before and the reason he can't sort. But you did pick out the
fact that the first Balance has a different formula than the rest
unless you do something like you suggested with ISNUMBER..

See http://www.mvps.org/dmcritchie/excel/insrtrow.htm
you need to use OFFSET so that you can insert/delete rows,
or sort rows.

Hi sokevin,

The pictures show show Credits with negative numbers
therefore your unusual formula of adding both Debits and Credits
into the balance. But actually you are adding Net (column I) to
the previous balance (column K, one row up).

K2: =i2 -- no previous balance:

The following will give problems with insertin/deleting and sorting
and should not be used because it has reference directly to two different rows.
K3: =K3 + i2

The following will allow inserting/deleting and sorting
K3: =OFFSET(K3,-1,0)+i3

If you want something that would allow you to sort from row 2 down
incorporating what Debra included where G1 would have a title i.e. 'Balance
then you can fill down.
K2: =IF(ISNUMBER(OFFSET(k2,-1,0), OFFSET(K2,-1,0)+i2, i2)



Debra Dalgleish said:
Use the following formula in cell K2, and copy down to the last row of data:

=IF(ISNUMBER(K1),K1,0)+J2
Is there a function where i can find the moving balance?

For example

i have DR and CR.

i can do Balance = DR cell + Cr Cell + Previose Balance


BUT whenI DO A SORT on the data, the column that contains the balance
formula will now not work

i attact link to pic of spreadsheet to explain things better


[image: http://member.telpacific.com.au/soke/general/msexcel1.jpg]

and after i sort the data....

[image: http://member.telpacific.com.au/soke/general/msexcel2.jpg]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top