Off Balance...

[artboyblue]

I am completely new to Excel, and need to do a ledger for work. I have been able to figure out nearly everything so far, but am having a problem with the "Balance" Column. Each cell in this column gets the running total from the cell directly above it, and then examines the data in its own row to the left, which it then adds or subtracts to create a new total, which it finally places in its own cell in the Balance column. The problem I have is two fold:

1. I need to have some empty rows available, as we have lots of transactions to add on a regular basis. These empty rows (and more specifically, all of the empty cells in the Balance column currently report the last calculated running balance... which is technically correct and is what the formula is asking. But, it would be much better if the formula for these cells could determine when the entire row to the left is empty, and then leave the running total column blank until the row is populated.

Here is the formula I have come up with... please don't laugh, I am a complete newbie. ;-)

=SUM(INDIRECT(ADDRESS(ROW()-1,COLUMN()))+(INDIRECT("E"&ROW()))-(INDIRECT("J"&ROW())),-(INDIRECT("K"&ROW())),-(INDIRECT("L"&ROW())),-(INDIRECT("M"&ROW())),-(INDIRECT("N"&ROW())),-(INDIRECT("O"&ROW())),-(INDIRECT("P"&ROW())),-(INDIRECT("Q"&ROW())),-(INDIRECT("R"&ROW())),-(INDIRECT("S"&ROW())),-(INDIRECT("T"&ROW())),-(INDIRECT("U"&ROW())),-(INDIRECT("V"&ROW())),-(INDIRECT("W"&ROW())),-(INDIRECT("X"&ROW())),-(INDIRECT("Y"&ROW())),-(INDIRECT("Z"&ROW())),-(INDIRECT("AA"&ROW())),-(INDIRECT("AB"&ROW())),-(INDIRECT("AC"&ROW())),-(INDIRECT("AD"&ROW())),-(INDIRECT("AE"&ROW())))

2. At the very end of the Balance column I need to display... well, the balance, regardless of how many rows have been completed. Right now, I think because of the first issue, I am getting a HUGE number that is WAY off. Here's the formula I am using:

=SUM(AF10:OFFSET(AF120,-1,0))

Thanks for any help you can offer.[/FONT][/SIZE]

[DonkeyOte]

It's not clear which row is the first containing the formula... if we assume it is in AF2 then perhaps you can revise your first formula to something along the lines of:

AF2:
=IF(COUNT($E2,$J2:$AE2),$AF1+$E2-SUM($J2:$AE2),"")
copied down to AF119

For your final row which is to show Total, assuming it is AF120

AF120:
=LOOKUP(9.99999999999999E+307,$AF$2:$AF$119)

[artboyblue]

Sorry for not being clearer... the first cell that shows the opening balance is AF10, then AF11 is the first full row for the ledger with the cell for the running total. Your formula for the final total at the bottom plugged in and worked perfectly... thanks much. Not sure I understand the solution you offered for the first part of he problem though.

[DonkeyOte]

OK so if we assume first formula in terms of running balance is to go into AF10 then:

AF10
=IF(COUNT($E10,$J10:$AE10),SUM($AF9,$E10)-SUM($J10:$AE10),"")
copied down to AF119

[artboyblue]

Thanks for your help. That worked. I revised the formula for next cell below like this:

=IF(COUNT($E11,$J11:$AE11),SUM($AF10,$E11)-SUM($J11:$AE11),"")

and it worked too. However, this is a huge table... please tell me there is an easier way than manually editing every cell in the balance column. This would be especially painful when adding new rows.

Thanks again for your assistance.

[DonkeyOte]

You can copy and paste AF10 into AF11:AF119 simultaneously - you need not apply each formula individually... (or if you hover on bottom right corner of AF10 you will see a black cross - click that and drag down to AF119).

You might want to google on "Fill Handle" as this is a very handy tool when you're getting to grips with XL.

[artboyblue]

Absolutely Astounding... I can't thank you enough. Worked like a charm. Thanks for being so patient, thorough, and quick to offer assistance... truly appreciated.

[zbor]

Please, make solved from drop down menu next to title you've changed

[artboyblue]

Gotcha... Done... Thanks Again.

[artboyblue]

I have a quick follow-up question, if I may. How can I modify the function you offered above =LOOKUP(9.99999999999999E+307,$AF$10:$AF$119) to self-adjust as we add new rows to the table?

Thanks again for helping this Excel Newbie in a real pinch... Sincerely.

[DonkeyOte]

Per the other question it's probably best to utilise INDEX (in preference to OFFSET given Volatility) so perhaps:

=LOOKUP(9.99999999999999E+307,INDEX($AF:$AF,10):INDEX($AF:$AF,ROWS($AF$1:$AF120)-1))

[artboyblue]

Yep, got it... works great. Now all of the formulas adjust themselves as we add new rows: columns, of course, would be another matter entirely, but we have been careful to at least try to be all-inclusive in the layout. So hopefully we will not need to add columns often.

Once again, thank you so kindly for your help. [/SIZE][/FONT]

[Vegita]

hi folks
i thought of opening a new thread, but since its same issue, i'll shoot right here, sorry if its not done properly.
I have same issue but more simple actually than the above.
column A is deposits
column B is withdrawal
Column C is balance

i used formula =SUM(A2,-B2) on column C1, then =SUM(C2,A3,-B3) on C2 and so on for C3, 4, 5....
Like above mentioned, Coumn C keep showing data even if A2, B2 are empty.
How could i i make so C2, 3, 4 etc show balance only if there is value entered in (A2 or B2) (A3 or B3) etc.

thanks again as this is great help, and did i mention i'm a newbie too...

[DonkeyOte]

Vegita,

Your post does not comply with Rule 2 of our Forum RULES.

Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.