If entered correctly (with the Ctrl + Shift + Enter shortcut), the formulas output the following results: In situation when you have two or more sets of independent x values, enter them in separate columns, and supply that entire range to the known_x's argument of the TREND fucntion.įor example, with the known_x 1 values in B2:B13, known_x 2 values in C2:C13, and known_y values in D2:D13, you use the following formula to calculate trend:Īdditionally, you can enter the new_x 1 and new_x 2 values in B14:B17 and C14:C17, respectively, and get the projected y-values with this formula: The below screenshot shows the calculated new y-values and extended trendline:Įxcel Trend formula for multiple sets of x-values After that, create a new line chart for the extended data set (B1:C17). To predict a trend for the future, you just need to include a set of new x-values in your TREND formula.įor this, we extend our time series with a few more month numbers and do trend projection by using this formula:Įnter the above formula in cells C14:C17 and remember to press Ctrl + Shift + Enter to complete it appropriately. To draw the trendline, select the sales and trend values (B1:C13) and make a line chart ( Insert tab > Charts group > Line or Area Chart).Īs the result, you have both the numeric values for the line of best fit returned by the formula and a visual representation of those values in a graph: To have it done, select the range C2:C13, type the below formula and press Ctrl + Shift + Enter to complete it: Based on this data, we want to determine the overall trend in the time series ignoring hills and valleys. In this example, we have the month numbers (independent x-values) in A2:A13 and sales numbers (dependent y-values) in B2:B13. Supposing you are analyzing some data for a sequential period of time and you want to spot a trend or pattern.

TREND formula for time series trend analysis in Excel

Since the new values are returned as an array, you won't be able to edit or delete them individually.Īt first sight, the syntax of the TREND function might seem excessively complicated, but the following examples will make things a lot easier.

If omitted, known_x's is assumed to be the array of serial numbers, which is a visual indication of an array formula.

If several x variables are used, known_y's must be a vector (one column or one row).

If only one x variable is used, known_y's and known_x's can be ranges of any shape but equal dimension.

Known_x's (optional) - one or more sets of the independent x-values. Known_y's (required) - a set of the dependent y-values that you already know.

Trendline excel equation