Weighted % from average of range

Hi,

I believe you might wanna do a multiple regression model, such as y = β1 + β2 * x2 + β3 * x3, is that right? Where β2 represents the impact of the maturing balance and β3 the impact of trend

There are some guides on the web to do that using add-ins that you could look into, searching "excel multiple correlation". It would help if you have some knowledge on the topic though

=sumproduct(b2:b19,c2:c19)/sum(b2:b19)

Originally Posted by Jdevil

Hi,

I believe you might wanna do a multiple regression model, such as y = β1 + β2 * x2 + β3 * x3, is that right? Where β2 represents the impact of the maturing balance and β3 the impact of trend

There are some guides on the web to do that using add-ins that you could look into, searching "excel multiple correlation". It would help if you have some knowledge on the topic though

Upon investigating regression I stumbled across putting in the following;

=FORECAST(B2,$C$2:$C$19,$B$2:$B$19) etc, which subsequently replots the points to the line of best fit. The R Squared isn't great but in theory at least it replots the retention %'s onto the line of best fit to remove the volatility. This doesn't exactly make it as if everything is even, but it may achieve something similar to what I want, I think?

Originally Posted by mosmosmos

Upon investigating regression I stumbled across putting in the following;

=FORECAST(B2,$C$2:$C$19,$B$2:$B$19) etc, which subsequently replots the points to the line of best fit. The R Squared isn't great but in theory at least it replots the retention %'s onto the line of best fit to remove the volatility. This doesn't exactly make it as if everything is even, but it may achieve something similar to what I want, I think?

Hi,

Yes, it seems to do its job at estimating the Retained from the Maturing Balance, but I'm not sure how you could derive a trend from that. If what you're looking for is to find a trend, then you're dealing with 2 variables at once.

I haven't tried, but this article I quickly looked at on Google: http://cameron.econ.ucdavis.edu/exce...egression.html

The intercepts you find in the table are basically what you are looking for, where one of the two variables would be your trend while the other is simply the impact of the maruting balance variation