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
  • Last Modified:

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
Asked:
mspcindc
  • 3
1 Solution
 
Robert SchuttSoftware 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
 
Robert SchuttSoftware EngineerCommented:
In the attached workbook I created a macro that does the calculation I describe for the examples you gave.
distributeddollars.xlsm
0
 
mspcindcAuthor 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
 
Robert SchuttSoftware 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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

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