Smoothing Data

Do you know what kind of smoothing algorithm you want to use?

The normal highs/lows in the Word document are very smooth -- making me think that they are the result of some kind of regression algorithm -- maybe even based on data for the entire year and not just January. I don't know if your goal is to exactly replicate this, or just come up with any kind of algorithm that gives smooth values like this.

If your goal is to exactly replicate this, you probably need to research how the source for the word document obtained those numbers so you can understand their algorithm and implement it here.

If your goal is to come with any suitable algorithm, I would probably start with a basic, empirical polynomial (quadratic) model. This can be accomplished fairly easy in Excel using the LINEST() function to generate the polynomial's coefficients. From those coefficients, it should be straightforward to generate the smoothed data.

LINEST() help file:https://support.office.com/en-us/art...a-fa7abf772b6d Pay particular attention to the example for a cubic polynomial near the end of the Remarks section -- just before the first main example.

It's not a definitive answer, but it should give you something to start with.

I can't upload a file at the moment. Does a linear/polynomial regression seem like what you want, or some other regression time? Was there something specific in the help file that you were unable to understand? Did you try any of the examples to get a feel for how the LINEST() function works?

Try this:

1) Select BN42:BP42.
2) Enter LINEST() function as described in help file =LINEST(BN5:BN35,A5:A35^COLUMN(A:B)) Remember that LINEST() is an array function, so it must be confirmed with ctrl-shift-enter.
3) In BM5, enter =$BP$42+$BO$42*A5+$BN$42*A5^2. Copy down.
4) Evaluate the results in BM5:BM35 to see if those are the results you want.