1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Help with Excel formula

Discussion in 'Business Applications' started by Aegis1, Apr 6, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. Aegis1

    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
     

    Attached Files:

  2. Anne Troy

    Anne Troy

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

    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
     
  4. Anne Troy

    Anne Troy

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

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

    Aegis1 Thread Starter

    Joined:
    Jan 3, 2004
    Messages:
    24
    thank I;ll zip it and edit my post...
     
  6. Aegis1

    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
     
  7. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,255
    First Name:
    Wayne
    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
     
  8. Aegis1

    Aegis1 Thread Starter

    Joined:
    Jan 3, 2004
    Messages:
    24
    Etaf, I would appreciated a copy of your spreadsheet thanks, Dennis
    [email protected]
     
  9. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,255
    First Name:
    Wayne
    sent sample spreadsheet
     
  10. Aegis1

    Aegis1 Thread Starter

    Joined:
    Jan 3, 2004
    Messages:
    24
    I'll check it out thanks I'll post back with results...Dennis
     
  11. Aegis1

    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
     
  12. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    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.
     

    Attached Files:

  13. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,255
    First Name:
    Wayne
    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
     
  14. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,255
    First Name:
    Wayne
    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
     

    Attached Files:

  15. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,255
    First Name:
    Wayne
    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
     

    Attached Files:

  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/217695

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice