Don't laught at me please; how to make Excel know last cell with info

Posted by Veronica P. on January 28, 2001 11:49 PM

Please do not make fun of me; my project is simple, even silly but I ran into an Excel problem that I can not solve and I find it kind of difficult to explain. I have column A and B as a simple way to update and reconcile my bank checkbook. In column A after the 2nd entry (which is a number-begining balance)
I have a formula that will add the prev bal with next entry of money (+ or -). I want instead to use the simple addition formula (which will give me the balance even when I have no more entries), to use something like this: if cell Bx is empty BUT not the LAST cell in range B1:B50 with an amount then add cell Ax+0 (zero) (because I get error otherwise) and if Bx is empty but the last one with amount, I want the result to be "", empty cell.
I use the range A1:B50; I have the formulas starting from A3 to A50; I need to get a result from that formula only when there is an amount in column B, and to get
"" in column A when B is empty.
A1 Balance B1 deposits/withdrawls
A2 2000 B2 300
A3 =IF(B2="",A2+0,A2+B2) B3 -200
A4 =IF(B3="",A3+0,A3+B3) B4 300
A5 =IF(B4="",A4+0,A4+B4) B5 empty
A6 =IF(B5="",A5+0,A5+B5) B6 200

Any help greatly appreciate. Thank you in advance.

Posted by Dave Hawley on January 29, 2001 12:19 AM

Hi Veronica

Wouldn't dream of laughing, It's perfectly valid question.

Try placing this formula in cell A2 and copy it down as far as needed. It will give you a running total in Column A

=IF(B2=COUNTA(B2:$B$5000)=1,A1,IF(OR(COUNTA(B2:$B$5000)=0,B2=""),"",SUM($B$2:B2)))

The absoluting with the Dollar signs is important.

While this maybe a way to get a running total Excel does offer more efficient ways via means of Data>Subtotals and Pivot Tables. Give them a go sometime.

Hope this helps
Dave
OzGrid Business Applications

Posted by Aladin Akyurek on January 29, 2001 1:02 AM

Enter

A3 =IF(B2,INDIRECT("$A"&COUNT($A$1:A2)+1)+B2,"")

Aladin