Help with Ledger/accounting formulas
 

I am using Excel 2003. I want to create a worksheet that has two columns:
one for expense and second for remaining cash. The worksheet has 31 rows for
31 days in a month. I want to input an expense on any given day, even
skipping days were no expenses occured and have only the cell next to the
expense cell show the remaining balance.

Example:
Day Budget amount $400.00
Expense Remaining Balance
1 $100 $300
2 (empty cell) (empty cell)
3 (empty cell) (empty cell)
4 $50 $250
5 (empty cell) (empty cell)
6 $100 $150
and so on, and so on, etc.

How can I accomplish this?

One way ..

Set-up
---------
Budget amount is in C1
The numbers for "Day": 1 - 31 is in A3:A33
Expense & Remaining Balance in adjacent cols B and C

Under the "Remaining Balance" col
---------------------
Put in C3: =IF(B3="","",$C$1-SUM($B$3:B3))
Copy C3 down to C33

Here's a link to a demo file with the implemented set-up:
http://www.savefile.com/files/5167654
File: Help with Ledger_acc_formulas_jam4jah_wksht.xls

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"jam4jah" wrote in message
...
I am using Excel 2003. I want to create a worksheet that has two columns:
one for expense and second for remaining cash. The worksheet has 31 rows
for
31 days in a month. I want to input an expense on any given day, even
skipping days were no expenses occured and have only the cell next to the
expense cell show the remaining balance.

Example:
Day Budget amount $400.00
Expense Remaining Balance
1 $100 $300
2 (empty cell) (empty cell)
3 (empty cell) (empty cell)
4 $50 $250
5 (empty cell) (empty cell)
6 $100 $150
and so on, and so on, etc.

How can I accomplish this?

jam4jah Wrote:
I am using Excel 2003. I want to create a worksheet that has two
columns:
one for expense and second for remaining cash. The worksheet has 31
rows for
31 days in a month. I want to input an expense on any given day, even
skipping days were no expenses occured and have only the cell next to
the
expense cell show the remaining balance.

Example:
Day Budget amount $400.00
Expense Remaining Balance
1 $100 $300
2 (empty cell) (empty cell)
3 (empty cell) (empty cell)
4 $50 $250
5 (empty cell) (empty cell)
6 $100 $150
and so on, and so on, etc.

How can I accomplish this?

Hi jam4jah

Assuming your data to be laid out like the table below

ColumnA ColumnB
Day Budget 400

100 300


50 250

100 150

In cell B2 enter this formula:- =IF(A2="","",$B$1-SUM($A$1:A2)), copy
this down columnB


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=400346

Thank You Max!!

jam4jah

"Max" wrote:

One way ..

Set-up
---------
Budget amount is in C1
The numbers for "Day": 1 - 31 is in A3:A33
Expense & Remaining Balance in adjacent cols B and C

Under the "Remaining Balance" col
---------------------
Put in C3: =IF(B3="","",$C$1-SUM($B$3:B3))
Copy C3 down to C33

Here's a link to a demo file with the implemented set-up:
http://www.savefile.com/files/5167654
File: Help with Ledger_acc_formulas_jam4jah_wksht.xls

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"jam4jah" wrote in message
...
I am using Excel 2003. I want to create a worksheet that has two columns:
one for expense and second for remaining cash. The worksheet has 31 rows
for
31 days in a month. I want to input an expense on any given day, even
skipping days were no expenses occured and have only the cell next to the
expense cell show the remaining balance.

Example:
Day Budget amount $400.00
Expense Remaining Balance
1 $100 $300
2 (empty cell) (empty cell)
3 (empty cell) (empty cell)
4 $50 $250
5 (empty cell) (empty cell)
6 $100 $150
and so on, and so on, etc.

How can I accomplish this?

Thank You Paul!

jam4jah

"Paul Sheppard" wrote:


jam4jah Wrote:
I am using Excel 2003. I want to create a worksheet that has two
columns:
one for expense and second for remaining cash. The worksheet has 31
rows for
31 days in a month. I want to input an expense on any given day, even
skipping days were no expenses occured and have only the cell next to
the
expense cell show the remaining balance.

Example:
Day Budget amount $400.00
Expense Remaining Balance
1 $100 $300
2 (empty cell) (empty cell)
3 (empty cell) (empty cell)
4 $50 $250
5 (empty cell) (empty cell)
6 $100 $150
and so on, and so on, etc.

How can I accomplish this?

Hi jam4jah

Assuming your data to be laid out like the table below

ColumnA ColumnB
Day Budget 400

100 300


50 250

100 150

In cell B2 enter this formula:- =IF(A2="","",$B$1-SUM($A$1:A2)), copy
this down columnB


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=400346

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"jam4jah" wrote in message
...
Thank You Max!!

jam4jah