• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Moving averages with gaps in a formatted table

S

Steffen19

New Member
Hi guys, I hope you can help me with my problem.


I´m working on a personal project where I´m trying to monitor different investments. I have the amount invested in the E-column, the potential return per unit in the F-column, the success rate of each investment in the G-column and the net profit in the K-column. The header of my table can be found in row 8, meaning the first investment can be found in row 9.


Nothing too fancy so far. What I want is to calculate a moving average of the final 10 investments in order to get an automatted recommendation (along with some other values) on the amount I should invest in the next available project.


Like I mentioned in the title, the problem is that there´s a time-gap between the start of an investment and its completion, meaning project #7 and #8 might be starting at the same time, however #8 finishes and #9 starts before #7 finishes as well. SO there will definitely be gaps when it comes to the results of each investment.


What I want now is a formula that gives me the average of the final 10 results in the K-column: If I´m currently looking to invest in #17, I want the moving averages of the last 10 projects from #7-#16, in case there has been a gap and one result isn´t in yet, I want the formula to automatically go back to #6 or more, up until it finds the last 10 results and calculates the moving average.


I hope you can help me with my problem and I´m very much looking forward to discussing it here with you.


Thanks
 
formula to go in row 9 of whatever column you are using for the average:

=AVERAGE(K9:INDEX(K:K,IFERROR(LARGE(IF(ISNUMBER(K9:K$9),ROW(K9:K$9)),10),9)))


Formula is an array, and needs to be confirmed using Ctrl+Shift+Enter.

The IFERROR function is there incase there are not 10 previous records. If you are manually accounting for this, formula can be reduced to:

=AVERAGE(K9:INDEX(K:K,LARGE(IF(ISNUMBER(K9:K$9),ROW(K9:K$9)),10)))
 
Wow Luke, thank you very much, that really works! What do I have to do if I want to keep everything the same except for the fact that I want "0" to be displayed up until the count reaches 10 investments?


Thanks again, you have been a great help!
 
=(AVERAGE(K9:INDEX(K:K,IFERROR(LARGE(IF(ISNUMBER(K9:K$9),ROW(K9:K$9)),10),9))))*(COUNT(A$9:A9)>=10)


By multiplying by a boolean check at the end, we'll either get our expected value if true (e.g, $15 * 1 = $15) or we'll get 0, since false evaluates to 0 (e.g., $15 * 0 = 0)
 
You must log in or register to reply here.
Back
Top