I'd use the TREND() worksheet function

https://support.office.com/en-us/art...3-9a7cf7b51ef1

Returns values along a linear trend. Fits a straight line (using the method of least squares) to the arrays known_y's and known_x's. Returns the y-values along that line for the array of new_x's that you specify.
Capture.JPG

Also, I don't think (and I'm not sure) that you want to consider the period counter as an independent variable

The TREND function could be wrapped in VBA


Note that it is an array entered function - you don't add the { ...} excel does when you use control-shift-enter

Capture.JPG