+ Reply to Thread
Results 1 to 5 of 5

Using Excel to Help with my Budget

  1. #1
    Registered User
    Join Date
    08-24-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    365
    Posts
    38

    Using Excel to Help with my Budget

    Hi, I have downloaded heaps of budget programs, non seem to be what I want.
    They all want to group into types or all ask for the Monthly amount
    Maybe someone here can help.
    I know Excel basics, but have tried to make what I want but have failed.
    I am an old bloke and having trouble with new tricks
    Just need 1 Row completed and I can copy and do the rest

    Column A Description
    Column B Price
    Column C Frequency A Drop Down Week or Month or Quarterly or Year, So User Selects this and then Column D,E and F are populated
    Column D is the Sum Calculation of B x the frequency to =Week
    Column E is the Sum Calculation of B x the frequency to =Monthly
    Column F is the Sum Calculation of B x the frequency to =Year

    So if I have a weekly Bill, I will know what it is weekly Monthly and Yearly
    With a TOTAL at the bottom of each of the D,E and F Columns

    Sample:
    Kids Allowance $20 Per Week (So the week will say $20 (B)) (Month will be (B) $20*52 /12) (Quarter will be (B) $20*52/4) (Yearly will be (B) $20*52)
    Netflix $9.99 Per Month (So the week $9.99*12/52) (Month will be $9.99) (Quarter will be $9.99*12/4) (Yearly will be $9.99*12)
    Rates $478.77 per Quarter (So week will be $478.77*4 /52) (So Month will be $478.77*4 /12) (Quarter will be $478.77) (Yearly will be $478.77*4)
    Car Insurance 927.10 Per Year (So week will be $927.10/52) (So Month will be $927.10 /12) (Quarter will be $927.10/4) (Yearly will be $927.10)

    Thanks in advance

  2. #2
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    696

    Re: Using Excel to Help with my Budget

    Please upload a sample sheet.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Using Excel to Help with my Budget

    Are you still using Excel 2007?

    I presume this is what you want:

    Excel 2016 (Windows) 32 bit
    B
    C
    D
    E
    F
    G
    H
    3
    Description Price Frequency Weekly Monthly Quarterly Yearly
    4
    Kids' Allowance
    $ 20.00
    Weekly
    $ 20.00
    $ 86.67
    $ 260.00
    $ 1,040.00
    5
    Netflix
    $ 9.99
    Monthly
    $ 2.31
    $ 9.99
    $ 29.97
    $ 119.88
    6
    Rates
    $ 478.77
    Quarterly
    $ 36.83
    $ 159.59
    $ 478.77
    $ 1,915.08
    7
    Car Insurance
    $ 927.10
    Yearly
    $ 17.83
    $ 77.26
    $ 231.78
    $ 927.10
    8
    $ 76.96
    $ 333.51
    $ 1,000.52
    $ 4,002.06
    Sheet: Sheet1

    I have added a lookup table for the multiplier:

    Excel 2016 (Windows) 32 bit
    K
    L
    3
    Frequency
    Multiplier
    4
    Weekly
    52
    5
    Monthly
    12
    6
    Quarterly
    4
    7
    Yearly
    1
    Sheet: Sheet1

    and then used variations of this formula (adjusting the header reference for each column - in red):

    =IF([@Frequency]="","",[@Price]*VLOOKUP([@Frequency],Lookup,2,0)/VLOOKUP(Budget[[#Headers],[Weekly]],Lookup,2,0))

    In case you are unfamiliar with tables, to add a new row, select the last cell in the rightmost (yearly) column above the subtotal and hit the TAB key.
    Attached Files Attached Files
    Last edited by AliGW; 09-08-2019 at 03:12 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    08-24-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    365
    Posts
    38

    Re: Using Excel to Help with my Budget

    Thanks Ali (AliGW),
    This is great, now I can find out where the $ are going.
    Much appreciated
    Thanks Heaps
    David

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Using Excel to Help with my Budget

    No worries.

    Are you still on Excel 2007? If not, please update your profile.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 03-15-2017, 03:14 AM
  2. Nested IF statement to show under budget, within a % of budget, over budget
    By clafleur in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-15-2015, 10:36 AM
  3. budget formula. 2 different formulas for yearly budget SUMIF?
    By italianstallion in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-04-2013, 05:20 AM
  4. [SOLVED] IF function to define over budget or under budget
    By mrose in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 06-15-2012, 02:57 PM
  5. Pulling weekly budget data into monthly budget
    By MarkRabbit in forum Excel General
    Replies: 4
    Last Post: 10-19-2008, 04:28 PM
  6. Divide Monthly Sales Budget to Day budget
    By Benedikt Fridbjornsson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-11-2006, 03:25 PM
  7. [SOLVED] Divide Monthly Sales Budget to Day Budget
    By Benedikt Fridbjornsson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2006, 11:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1