check book balance

stevotsky

New Member
Joined
Oct 15, 2006
Messages
11
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?
 

Some videos you may like

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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Apr 25, 2004
Messages
1,213
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Apr 25, 2004
Messages
1,213
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 15, 2006
Messages
11
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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 15, 2006
Messages
11
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
Joined
Apr 25, 2004
Messages
1,213
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,112,937
Messages
5,543,095
Members
410,583
Latest member
gazz57
Top