# 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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### 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
12
Views
567
Replies
1
Views
30
Replies
1
Views
190
Replies
4
Views
195
Replies
1
Views
86

1,141,429
Messages
5,706,405
Members
421,447
Latest member
arthuro2021

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back