Excel Formula Question

Factual Questions
1

I’m trying to design an electronic check register using excel because I’m not happy with the ones out there that I’ve found, and I want to see if I can. I’ve had no problems with it so far, but I’ve ran into one issue thats stumped me and I’m hoping that the Dope can help me out.

I want there to be a cell on top of the worksheet that always gives the current balance in my register. So as I add transactions in the register the amount in this cell will always reflect the last cell in the “balance” column that has data in it.

It seems like it would be an easy thing to do but for the life of me I can’t figure it out, and I’d appreciate any help or guidance you could give.

Thanks in advance all.

2

If you wanted to sum all of column B in Excel 2007, you can use =SUM(B:B). Of course, that formula can’t be in column B. I’m not sure about earlier versions.

3

That would work except I have all of the calculations done in formulas so the only thing in that Column is the balance. But if I can’t get this to work I guess thats an option, but it would double the size I need so I’ll avoid it if at all possible.

4

I assumed that cells with no entries for checks or deposits yet are truly blank and not just filled with formulas. If that’s the case, you can use the indirect function and the counta function together to generate the address of the last cell with data in it (the column must be dense, though: no blank cells).

So, say your running balance is in column C and the first row of entries is row 2. Your formula for the current balance cell would be


=INDIRECT("C"&1+COUNTA(C:C))

This counts the nonblank cells in column C, adds 1 to that number (to make up for the blank cell C1) and tells the cell to display the contents of cell Cn where n is that number.

5

Let me take another stab at that for clarity and to add a later thought:

I am assuming that rows without entries for checks or deposits are actually blank, i.e., not pre-filled with formulas.

If that is the case, you can quite easily use the indirect function and the counta function together to generate the address of the last cell with data in it (the column must be dense, though: no blank cells between the first cell with data and the last cell with data).

So, say your running balance is in column C and the first row for checks & deposits is row 2 (presumably, this means that cell C1 is blank). Your formula for the current balance cell would be


=INDIRECT("C"&1+COUNTA(C:C))

This counts the nonblank cells in column C, adds 1 to that number (to make up for the blank cell C1) and tells the cell to display the contents of cell C# where # is that number. (If for some reason cell C1 is not blank, just leave the “1+” out.)

If the above assumption about pre-filled formulas is not the case, you can still manage it. Write the “current balance” formula like so:


=C2+SUM(B:B)-SUM(A:A)

where cell C2 is your starting balance, column A contains your checks and column B contains your deposits. Starting balance plus all deposits minus all checks will be your current balance.

Knead
Forcing Excel to do a database’s job since 1993