Creating Budgetary Spreadsheet

Discussion in 'Software Discussion & Support' started by Kov-Ice, Jul 28, 2007.

  1. Kov-Ice

    Kov-Ice

    Joined:
    Dec 27, 2001
    Messages:
    3,353
    Location:
    St. Louis, Missouri
    I have only minimal experience with spreadsheets like Excel. Here's what I'd like to do, if anyone can offer suggestions...

    I'd like to have a budget category with the monthly allowance at the top of a column. I'd like to be able to input the amount of purchases in the boxes below. Ideally, I'd like to have a box for a running total somewhere so that with each entry, the updated amount remaining is automatically shown.

    Hopefully, that makes sense. Seems like it should be easy, but I can't seem to get it to happen. Ideas? Thx.
     
  2. Force Flow

    Force Flow Barefoot on the Moon!

    Joined:
    Aug 23, 2002
    Messages:
    14,149
    Location:
    Northeastern USA
  3. Stuey

    Stuey Defenestrator

    Joined:
    Jan 4, 2005
    Messages:
    1,371
    Location:
    NYC & NJ
    I tried to make a simple one for you. Play around with the numbers and check out the formulas that I used, then play around with everything else when you have the hang of it.
     

    Attached Files:

  4. Kov-Ice

    Kov-Ice

    Joined:
    Dec 27, 2001
    Messages:
    3,353
    Location:
    St. Louis, Missouri
    Cool. Stuey, excellent stuff. I'm looking to create something simple almost exactly like the example.

    Here's a question about it for anyone with a bit o' knowledge....

    In the balance column, in order to get the running total to update, what's the best way to replicate that formula downward? If the first few boxes have the formula added manually, is just using Ctrl-D to duplicate as far down as necessary the workable solution? Or is there any easier or better way?
     
  5. pillainp

    pillainp

    Joined:
    Jul 3, 2002
    Messages:
    800
    Location:
    Trivandrum, India
    Two ways to go about this:
    1) Select the last cell that contains the formaula and extend your selection as far down as you want (the selection should include the last cell that contains the formula), and then press CTRL+D to fill all the empty cells in the selection with the formula.

    2) Click on the last cell that contains the formula. Hover your mouse over the little black dot at the bottom right corner of the cell till it turns to a dark black +. Then click and drag as far down as you want to go to replicate the formula in the empty cells lower down the column.


    You can do something even more elegant. Simply modify Stuey's formula for the Balance column to include the AND function and you can set it up so that the balance column will only show a number if there is data in either the "Purchases" or "Credit" column. Like so:
    =IF(AND(En<=0,Gn<=0),"",I(n-1)-En+Gn)
    where n and n-1 are the row numbers.

    Just fill that formula into cell I3 of the "Balance" column and then use one of the methods above to extend that formula throughout the "Balance" column as far down as you want.

    NPP
     
    Last edited: Jul 29, 2007
  6. Kov-Ice

    Kov-Ice

    Joined:
    Dec 27, 2001
    Messages:
    3,353
    Location:
    St. Louis, Missouri
    Excellent addition, pillainp. Thanks to you and Stuey for your tutelage. I think I have it configured exactly as I need it now.
     
  7. Stuey

    Stuey Defenestrator

    Joined:
    Jan 4, 2005
    Messages:
    1,371
    Location:
    NYC & NJ
    I'm glad that it helped! To be honest, that's just about as far as my Excel skills go.

    Also, to duplicate a forumula but using inputs from subsequent rows, copy and past works in addition to the mentioned dragging methods.
     
  8. Kov-Ice

    Kov-Ice

    Joined:
    Dec 27, 2001
    Messages:
    3,353
    Location:
    St. Louis, Missouri
    Well, the help from you guys pushed me to learn a lot. I've totally tailored several sheets in a document to fit our monthly and yearly budget. Thx again!