check book balance

[stevotsky]

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?

 

[Scott Huish]

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]

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]

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]

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]

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]

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]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	BILLS ACCOUNT TO 30/6/2007																NB ENTER EXPENSES AS A NEGATIVE				A POSITIVE EQUALS CREDIT BALANCE
2	RUNNING BALANCE		$13,418.31			MBF	ENERGY	RATES	CAR REGO	CAR INSCE	BOAT REGO	BOATINSC	van	HOUSE IN	PHONE	FEES	M/V REP	PLAN GIV	SUNDRY	Open Bal	MONEY			DEBIT	CREDIT	BALANCE
3				BUDGET		2200	1800	2200	1100	700	50	300	400	1200	5000	1000	5500	840			5450.98		22290
4	1/07/2006	ACCOUNT BALANCE BANK 30/6//06																							3812.12	3812.12
5	1/7/065	Initial balance tfr																		10000				0	10000	13812.12
6	3/07/2006	Refund medical re tripo injections														201.6								0	201.6	14013.72
7	10/07/2006	Elgas					-87.1																	-87.1	0	13926.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