need formula? to include blank cells in "running balance"

T

teprps - ko

I have debits (column a) and credits (column c) with a running balance
(column e). How do I leave a blank space in the running balance when there
is a blank row between entries? Note: some entries use multiple consecutive
rows.
 
T

teprps - ko

This works only if both the debit and credit cells contain data. However, in
an accounting entry one row has a debit entry - leaving a blank credit cell.
The next row might have a credit entry - leaving the debit cell blank.
Therefore, with your formula when it sees a blank cell it ignores the data
cell and the balance cell is blank. I need it to work like this:
i.e. A1 = 4, C1 = 0, E1 = 4
A2 = 6, C2 = 0, E2 = 10
A3 = 0, C3 = 1, E3 = 11
Row 4 is blank
A5 = 0, C5 = 3, E5 = 14

Any other ideas???
 
T

teprps - ko

This is from a report that I am exporting into ExceL.
If the A or C cell has a zero value the cell is blank.....but still I need
to figure in the number in the other cell. In the following: E1,E2,andE3 are
one entry; E5 and E6 are another entry; E8 is part of another entry.......

E1: =sum(A1,-C1)
E2: =sum(E1,A2,-C2)
E3: =sum(E2,A3,-C3)
row 4 is blank
E5: =sum(E3,A5,-C5)
E6 =sum(E5,A6,-C6)
row 7 is blank
E8 =sum(E6,A8,-C8)
etc., etc., etc.,..........

Note: sometimes the entries are 2 rows and sometimes they are 20 or more
rows long. I need to continue the running balance (Column E) in spite of the
blank rows between entries. And I need to fill the formula down the E column
as there are sometimes hundreds of entries so I cannot modify each "row
formula". It would be simple if there were no blank rows...... Also, if
possible to "look pretty" I'd like the E cells in the blank rows to also look
blank. Have I given you enough?????? If you can figure this one, you are a
genius in my book!
 
M

Max

Give this a try

In E1: =SUM(A1,-C1) [no change]
In E2:
=IF(AND(A1="",C1=""),SUM(OFFSET(E1,-1,),A2,-C2),SUM(E1,A2,-C2))
Copy E2 down as far as required

Then to mask it in col E so that it appears blank for the "blank rows",
use CF with font set to white color (ie to match with "white" no fill color)

Select col E (ie with E1 active),
click Format > Conditional Formatting
Condition 1, Formula Is: =AND(A1="",C1="")
Click Format button > Font tab > Select white font color > OK out
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
 

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