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

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.

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

Maybe something like this:
=IF(COUNT(A2,C2)<2,"",<yourformula)
which returns "blanks", ie "" unless cols A and C contain numbers
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"teprps - ko" wrote:
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.

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

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???

"Max" wrote:

Maybe something like this:
=IF(COUNT(A2,C2)<2,"",<yourformula)
which returns "blanks", ie "" unless cols A and C contain numbers
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"teprps - ko" wrote:
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.

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

Post your point formulas
In E1: ?
In E2: ?
E2 is then copied down?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"teprps - ko" wrote
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???

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

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!

"Max" wrote:

Post your point formulas
In E1: ?
In E2: ?
E2 is then copied down?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"teprps - ko" wrote
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???

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

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
---
"teprps - ko" wrote:
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!