# check book balance

#### stevotsky

##### New Member
i have created a check book ledger - and i would like a cell at the top to show me what the balance is so that i don't scroll down to look at the balance. this cell should look in the column titled BALANCE for the last number in that column. Formula suggestions please?

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Scott Huish

##### MrExcel MVP
Hi, Welcome to the board!

Try this:

=LOOKUP(9.9999999999999E+307,BALANCE)

I wasn't sure if you're column had a name of BALANCE, if so, you should be able to use this as is, otherwise replace BALANCE with your range

#### pedro-egoli

##### Well-known Member
HOTPEPPER,
One of those mysteries of life when I tried your formula.

On a blank sheet I substituted B1:B100 for BALANCE and it worked perfectly.
Formula shows
=LOOKUP(9.9999999999999E+307,Z4:Z250)

On my cheque book ledger I tried it and included Z4:Z250 to cover BALANCE and result was a BLANK cell (C2).

Cell C2 is formatted General .

Any clues as to my problem?

Pedro

#### Scott Huish

##### MrExcel MVP
Possibly,

When a line has no entries are you returning 0 to the cell and is your option for displaying zero values unchecked?

#### pedro-egoli

##### Well-known Member

Thanks HOTPEPPER,
You were right on both counts.
Added formula to return a blank in balance column rather than "0" and ticked the zero box.
Your assistance once again appreciated

Pedro

#### stevotsky

##### New Member
Many thank!

Many thanks for the help - I tried it in Excel and it is perfect. I have never used LOOKUP so there is no way I could have come up with that! I am trying to use Google spreadsheet(s) so that my girlfriend and I can update our checkbook .... and we can check things as we check mail- the Google spreadsheet is not accepting the formula for some reason - so i will do some reading on. It appears that LOOKUP is not supported per http://docs.google.com/support/spreadsheets/bin/static.py?page=functionlist_en.html#COLUMN

Once again - thanks for the help!

Hi, Welcome to the board!

Try this:

=LOOKUP(9.9999999999999E+307,BALANCE)

I wasn't sure if you're column had a name of BALANCE, if so, you should be able to use this as is, otherwise replace BALANCE with your range

#### Scott Huish

##### MrExcel MVP

The other way to get this of course, is to take your starting balance and add the sum of your Deposit column and subtract the sum of your payment column.

#### stevotsky

##### New Member
HAHAHA! LOL!

I saw your new solution - and all I could do is laugh out loud for a long time! I spent hours yesterday trying to fight Google spreadsheet(s) - and you had this simple solution that I could embrace any day! In my world, you are a genius!
God bless!

The other way to get this of course, is to take your starting balance and add the sum of your Deposit column and subtract the sum of your payment column.

#### pedro-egoli

##### Well-known Member
Thanks to a few members on forum the following spreadsheet is what I use for a Bills Account.
Hopefully formulae will show up and this enables the debit/credit/balance columns to be filled automatically once amounts are entered in sub headings
Bills accound 2001 onwards.xls
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1BILLS ACCOUNT TO 30/6/2007NB ENTER EXPENSES AS A NEGATIVEA POSITIVE EQUALS CREDIT BALANCE
2RUNNING BALANCE\$13,418.31MBFENERGYRATESCAR REGOCAR INSCEBOAT REGOBOATINSCvanHOUSE INPHONEFEESM/V REPPLAN GIVSUNDRYOpen BalMONEYDEBITCREDITBALANCE
3BUDGET22001800220011007005030040012005000100055008405450.9822290
41/07/2006ACCOUNT BALANCE BANK 30/6//063812.123812.12
51/7/065Initial balance tfr1000001000013812.12
63/07/2006Refund medical re tripo injections201.60201.614013.72
710/07/2006Elgas-87.1-87.1013926.62
2007 NEW FORMULA

Could not see how to get formula from sheet so here they are
X5 =IF(ISBLANK(A5),"",SUMIF(F5:U5,"<0"))
Y5 =IF(ISBLANK(A5),"",SUMIF(F5:U5,">0"))
Z5 =IF(ISBLANK(A5),"",IF((X5=0)*(Y5=0),0,SUM(Z4+X5+Y5)))

Copied down each column
If anyone can tell me how to view the formulae in these HTML spreadsheets it would be helpful.
Pedro

Replies
4
Views
36
Replies
5
Views
52
Replies
0
Views
70
Replies
3
Views
93
Replies
0
Views
60