+ Reply to Thread
Results 1 to 6 of 6

Producing a table of monthly values based on a monthly growth rate and yearly total

  1. #1
    Forum Contributor
    Join Date
    06-25-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    179

    Producing a table of monthly values based on a monthly growth rate and yearly total

    Hello,I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What i want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. I hope this makes sense ii cant attach a spread sheet yet but i will upload one to any service you suggest so you can see it. If you need any more clarification please email me. i'm just a little stumped on how to do this. It's also important to note that it restarts each year.

    Link for excel file is here:EXAMPLE FOR MISTER EXCEL.xlsx
    Last edited by joshnathan; 03-06-2013 at 12:23 PM.

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Producing a table of monthly values based on a monthly growth rate and yearly total

    Hi -

    Attached is a spreadsheet that does what you are asking. However, your %change is simply the number of days between subsequent months divided by the datevalue of the month. I'm not sure how that is measuring change, or change of what. For example, you can look at February of each year and it declines by about 0.001% each year because you are dividing a fixed number of days (31 for January in this case) by Excel's datevalue for January of each year. Just an observation. I assume you know what you are doing.

    Hope this helps.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    06-25-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    179

    Re: Producing a table of monthly values based on a monthly growth rate and yearly total

    Hey thanks for your input I actually got the figure from a polynomial interpolation of the yearly data to produce monthly figures that I used the delta of to those pleats points to produce those monthly changes u see there. Basically I'm trying to produce a set of monthly data points for the yearly data that I have

  4. #4
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Producing a table of monthly values based on a monthly growth rate and yearly total

    OK -

    I won't argue with you. It just seemed odd that the sum of the monthly data doesn't add up to the annual total.

  5. #5
    Forum Contributor
    Join Date
    06-25-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    179

    Re: Producing a table of monthly values based on a monthly growth rate and yearly total

    Hello Sorry,
    I just had a chance to look at the data on my computer instead of my phone and i realised that this doesn't actually do what i was intending for it to do.
    i wanted to calculate a january figure (a base figure) that would then have the feb % added to it. the march figure would be the jan+(jan+feb %) + (feb +march %) and so on untill it hits the total for the year. Hope that makes sense.

    the example here is amended to what i meant it to do. [ATTACH]219063[/ATTACH
    Attached Files Attached Files
    Last edited by joshnathan; 03-07-2013 at 08:39 AM.

  6. #6
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Producing a table of monthly values based on a monthly growth rate and yearly total

    Hi -

    Based on the example you sent, I have modified the spreadsheet. I don't understand the theory behind the mathematics to work the problem backwards (i.e, given a total determine the monthly amounts). However, I did set up the spreadsheet so you can enter the January amount, and by trial and error adjust the January amount until the sum of the months equals approximately the total for the year. I have completed the first two years and left the remaining ones blank. So, you would enter in Cell G27 your first guess (I would start with 26 or 27) and change your guess up or down until the sum is equal to the total of 370.78 for Year 2002.

    Hope this helps.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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