Help with Excel formula

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Aegis1

Thread Starter
Joined
Jan 3, 2004
Messages
24
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

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
Don't understand how row 5 and row 21 correlate.
Do you have a sample spreadsheet you can attach?
 

Aegis1

Thread Starter
Joined
Jan 3, 2004
Messages
24
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
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
The allowed extensions: bmp doc gif jpe jpeg jpg pdf png psd txt zip

So, just zip the file and upload it. :)
 

Aegis1

Thread Starter
Joined
Jan 3, 2004
Messages
24
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
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,471
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
 

Aegis1

Thread Starter
Joined
Jan 3, 2004
Messages
24
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
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,471
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
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,471
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

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,471
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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top