Tech Support Guy banner
  • Please post in our Community Feedback thread for help with the new forum software! If you are having trouble logging in, please Contact Us for assistance.
Status
Not open for further replies.
1 - 15 of 15 Posts

·
Registered
Joined
·
24 Posts
Discussion Starter · #1 ·
Need help in creating a formula to do the following: I need a column to show all deposits made and also a way to show a running balance whenever a entry is made in column (F) to subtract from total balance available no matter which entry is made in column (F) i.e amt paid in row 5 subtracts from column H then entry in row 21 is made that subtracts from balance then entry in row 9 subtracts etc,etc, .... would appreciate any help you can offer.
I running XP with excel 2000. .Thanks Dennis
 

Attachments

·
Registered
Joined
·
24 Posts
Discussion Starter · #3 ·
Good afternoon Dreamboat, I do have a XL spreadsheet but when i try to attach it it states invalid attachment. the rows are the payments i make each month. need the pymts to subtract from running balance in column (H). the pymts are made on different dates so i need to enter them as they are paid. Dennis
 

·
Registered
Joined
·
24 Posts
Discussion Starter · #5 ·
thank I;ll zip it and edit my post...
 

·
Registered
Joined
·
24 Posts
Discussion Starter · #6 ·
Dreamboat, whenever a bill comes in say auto withdraw on row 5 it's entered shoild subtract from balance in column H then when i pay house pymt row 15 tht will subtract next if another pymt is made at the top of the spreadsheet i want that t subtract also. I don't need it to subtract in order from row 4 to 5 tto 6 i nedd it to jump around row 5,15,4,20,9 etc. etc.. hope this helps Dennis
 

·
Super Moderator
Joined
·
65,526 Posts
i use this balance sheet for deposits and expenses.

heres the formula I use

=IF(AND(D3<>"",D4=""),SUM($D$3:E3),"")

D3 = revenue entries
D4 = expense (-ve)

i then copy the formula all the way down the sheet.

whenever I piut an expense in I also put zero in revenue.

Revenue..........Expense ...............running total
£30.00
£10.00
£25.00
£0.00 .............-£89.78
£40.00
£40.00.....................................£990.46

the ....... where to try and keep columns in the html post

the formula goes down the running total column

happy to send a copy of spreadsheet if required
 

·
Registered
Joined
·
24 Posts
Discussion Starter · #8 ·
Etaf, I would appreciated a copy of your spreadsheet thanks, Dennis
[email protected]
 

·
Registered
Joined
·
24 Posts
Discussion Starter · #10 ·
I'll check it out thanks I'll post back with results...Dennis
 

·
Registered
Joined
·
24 Posts
Discussion Starter · #11 ·
It's not working the way I want it to .. whenever i enter amts in (0)revenue and 50.00 in expenses it adds them instead of subtracting on the balance column
 

·
Registered
Joined
·
11,755 Posts
Problem.

You said you wanted a running total, when in fact you'd need 3 running totals and I don't see any EASY way to do it.

In the attached file, I provide a different layout, but also a method of summarizing the data into groups. See what you think.
 

Attachments

·
Super Moderator
Joined
·
65,526 Posts
you have to put a negative number in for expenses so revenue 0 expense -50.00

or if you modify the fomula and copy down.
so instead of =IF(AND(D3<>"",D4=""),SUM($D$3:E3),"")
change to =IF(AND(D3<>"",D4=""),(SUM($D$3:D3)-sum($E$3:E3)),"")

then it will add everything in col D and subtract Col E

I have sent a new version
=IF(AND(D3<>"",D4=""),SUM($D$3:D3)-SUM($E$3:E3),"")

so a : and next to a D = face so again :D
=IF(AND(D3<>"",D4=""),SUM($D$3 : D3)-SUM($E$3 : E3),"")
ignore the spaces in brackets this was to avoid the face
 

·
Super Moderator
Joined
·
65,526 Posts
OK re reading you request I have modified the formaula my original spreadsheet required the data to be entered sequentially, so I have added a test value to see if any entry has been entered on any row and then put the subtotal against that row.

=IF(G3=0,"",IF(AND(D3<>"",D4=""),SUM($D$3: D3)-SUM($E$3:E3),""))

again fixed for faces :)

and in column G I have
=IF(OR(D3<>"",E3<>""),1,0)

however if column D does not have an entry then the subtotal remains in the row above , i think about this.

worked out how to attach the file so attached here
 

Attachments

·
Super Moderator
Joined
·
65,526 Posts
OK so having looked at your sample and re-read the question I have attached another sample using your template.

this will test to see if there is a balance in any cells column B or an amountpaid in Column F and which ever is the highest row number it will display a balance calculated on the totals in column B (which i dont think you want this but...) and subtract the total from column F amount paid....

see test formula1.xls

so if all you want is a running balance in rowF whever its entered and then displayed at the lowest point then -
see test formula2.xls

otherwise I really do not understand the requirements
 

Attachments

1 - 15 of 15 Posts
Status
Not open for further replies.
Top