Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 462

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

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!
distributeddollars.xlsx
0
mspcindc
• 3
1 Solution

Software EngineerCommented:
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.
0

Software EngineerCommented:
In the attached workbook I created a macro that does the calculation I describe for the examples you gave.
distributeddollars.xlsm
0

Author Commented:
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.
0

Software EngineerCommented:
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 ;-)
0

## Featured Post

• 3
Tackle projects and never again get stuck behind a technical roadblock.