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.

Solved: Can someone help me with this Excel spreadsheet?

Discussion in 'Business Applications' started by jmk909er, Dec 4, 2013.

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

    jmk909er Thread Starter

    Joined:
    May 20, 2009
    Messages:
    354
    I have uploaded a sample of the spreadsheet I am trying to modify and I kinda have it working the way I want and I need these costs broken out the way I have them. I will try to explain what I want the best I can so bear with me.

    I want "Total" to capture if it is an expense or income. (right now it is not)

    I want "Balance" to reflect if it is an expense or income. (right now it is not)

    I want any unpopulated cells to show nothing or ""

    This is probably simple for an excel wizard but I only know excel good enough to get into trouble!

    Any help is appreciated, thanks, Joe
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,180
    First Name:
    Wayne
    sorry not really sure what you want to do on the spreadsheet - can you show on a sheet wjat you would like 2013 sheet to actually look like ?

    apart from row 14 , i see no income
     
  3. jmk909er

    jmk909er Thread Starter

    Joined:
    May 20, 2009
    Messages:
    354
    Hi, sorry I did not give a very good example I have uploaded a better example of row 14. This is what I am after but I did the math manually to increase the balance.
     

    Attached Files:

  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,180
    First Name:
    Wayne
    you can use
    =IF(SUM(B2+D2+E2)=0,IF(C2="","",C2),SUM(B2+D2+E2))
    in F2 and copy down - this will add the income
    BUT the balance , I do not know how you have $2,193.06
    to start with

    you need to start the sheet with whatever balance is available from previous years and
    if you put that in the first row as an income
    then you could use
    =SUM($C$2:C2)-SUM($B$2:B2)
    this will add up all the income and subtract the expense put into I2 and copy down
     
  5. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    I've uploaded a version of your data.


    What I feel you need to do is to sack the idea of multiple sheets for years and instead. Work with a single ordered datasheet. This makes it much easier to analyse and you could use a pivot table for example. Or, as I've done, add some basic summary data to the top of the sheet. The charts etc all work on the number of IDs ... so as you add more, the chart will adapt, as will the calculations.


    You're supplying a date (which makes year sheets unnecessary) and now you explicitly state something is an expense or income item. You also get into the habit of putting expense items as debits ($-) and income as credits ($+). That way all your running balance has to do is to add the latest credit or debit from the previous line's running balance. The formula I have used is not a straight sum, because if you were to use a line above + left type sum you'd have difficulties if you sorted the datasheet in a different order. The formula I use looks for the ID number preceding the current line and then adds the "Total" (+/-) form the current line.


    Anyway, it's just a thought. It looks like you're just embarking on this project, so I thought a different view at this stage might be beneficial.
     

    Attached Files:

  6. jmk909er

    jmk909er Thread Starter

    Joined:
    May 20, 2009
    Messages:
    354
    Wow Codelexicon you are awesome! Way more than I expected. This is way advanced for me...can you tell we what this is doing in J column?: =INDEX($J:$J,MATCH((A26-1),$A:$A,0))+I26

    I would like to be able to under stand it a little bit.
     
  7. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    Well, that formula is just:


    checking for the ID from the current record (in column A) and taking away 1 to get to the previous record and then (from column J, returning the Running Total + the latest (+ or -) Total (from column I)


    Chandoo is good at explaining it ...


    Any further help you need, just let me know


    :)
     
  8. jmk909er

    jmk909er Thread Starter

    Joined:
    May 20, 2009
    Messages:
    354
    Thanks so much, you have inspired me and I am creating and adapting into exactly what I want. I am trying to populate some data. Is there a way to convert a column of costs like $100.00 to -$100.00 or do I need to do this one at a time?
     
  9. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    Sure - there's a simple way


    type -1 in a spare cell then select that cell > copy


    select the range you need to convert


    select paste special > values operation should be Multiply


    click "OK"


    what you're asking excel to do is multiply all your values by your -1 ...
     
  10. jmk909er

    jmk909er Thread Starter

    Joined:
    May 20, 2009
    Messages:
    354
    It worked great. It populated the empty cells though with 0.00 is there a way to find and replace with "" so that those stay blank?
     
  11. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    You can use conditional formatting to do that ... I did it in the file i uploaded. Have a look at the rules.
     
  12. jmk909er

    jmk909er Thread Starter

    Joined:
    May 20, 2009
    Messages:
    354
    Is there a way to make the cells under "TYPE" have a drop down for INCOME and EXPENSE to populate the cells?
     
  13. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    Sure


    Somewhere in the sheet, or on another sheet, type the two words Expense and Income, one above the other.


    Then, select the cells where you want the drop down to be.


    Go to Data > Data Validation

    Allow "List"
    Source - select the icon next to the text input box, select the two cells you've just put Expenses and Income in and hit enter


    Now, you should have a drop down choice of Expenses or Income


    :)
     
  14. jmk909er

    jmk909er Thread Starter

    Joined:
    May 20, 2009
    Messages:
    354
    Hey CodeLexicon, everything is working beautifully! You have been such a great help. I am going to mark this as "Solved" Thanks Again!
     
  15. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    You're very welcome. Pleased you feel you've made progress with it. :)
     
  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!

Thread Status:
Not open for further replies.

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

  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