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?
 

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
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,217
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,217
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,217
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
 

Forum statistics

Threads
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?

Disable AdBlock

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
Top