Help with Excel formula

[Aegis1]

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

 

[Anne Troy]

Don't understand how row 5 and row 21 correlate.
Do you have a sample spreadsheet you can attach?

 

[Aegis1]

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]

The allowed extensions: bmp doc gif jpe jpeg jpg pdf png psd txt zip

So, just zip the file and upload it.

 

[Aegis1]

thank I;ll zip it and edit my post...

 

[Aegis1]

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]

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]

Etaf, I would appreciated a copy of your spreadsheet thanks, Dennis
dxe52@sbcglobal.net

 

[etaf]

sent sample spreadsheet

 

[Aegis1]

I'll check it out thanks I'll post back with results...Dennis

 

[Aegis1]

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]

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.

 

[etaf]

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$33)-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$33)-SUM($E$3:E3),"")

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

 

[etaf]

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

 

[etaf]

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