Excel 2007 How to calculate dynamic period of values from an array with set period percentages

Posted on 2012-09-17
Last Modified: 2012-09-18
I would like help distributing a set number of percentages which equal 1 over a dynamic number of periods.  

For example:  My Array of Period Percents = MyValArray(.05,.05,.05,.05,.1,.05,.2,.2,.1,.15)

However, the number of days in my new values is not 10 but 18 days.  How do I convert the above percentages to distribute the same way (however diluted) over the 18 days?  I am hoping there is a distribution or other statistical formula in excel that I will be able to use.  Also, I might also have the opposite problem where I am distributing over less than 10 periods.  For example:  I know that if I shortened my time period to 5, my values would be Period 1: .10, Period 2: .10, Period 3: .15, Period 4: .4 and Period 5: .25 based on the above array however I do not know the formula or the vba code to calculate it.  Thank you ahead of time - you guys are great!
Question by:mspcindc
    LVL 35

    Expert Comment

    by:Robert Schutt
    How I would do it manually: to dilute the 10 percentages over 18 periods you can do for the first period: F5*10/18, for the second period F5*8/18 + F6*2/18, that uses up the rest of F5 and takes enough of F6 to fill the second period with 10/18 of the total. Keep doing that and you get to 1 after 18 periods. Same for 7 but 'the other way around' (*10/7). It should be possible to make a mathematical function but it would be easier (for me) in VBA.
    LVL 35

    Accepted Solution

    In the attached workbook I created a macro that does the calculation I describe for the examples you gave.

    Author Closing Comment

    Wow!  That was fantastic.  Thank you.   I will need to study it to completely understand the code however I am able to use it seamlessly.  Thank you - my code was becoming exhausting to accomplish what you did much more succinctly.
    LVL 35

    Expert Comment

    by:Robert Schutt
    If there's any problem, just shout. It was very late last night but it was fun to do! I tried to make a visual representation but didn't get it myself... I'm more of a numbers guy apparently. The description in my first post does still seem accurate to me and exactly what I programmed in the end, but there were a few steps in between that didn't work for one or the other (more periods vs less periods) so your examples were very important in getting this right! If I could 'upvote' the question I would ;-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    This article will show, step by step, how to integrate R code into a R Sweave document
    Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now