Trend Function ExcelIn today's technologydriven world, markets and customer needs are changing so rapidly that it is essential that you move with trends and not against them. Trend analyses are very useful since they help look for the underlying patterns in the past and present data actions and predict future project behavior. In this tutorial, you will learn what the Trend function is, its syntax, parameter, points to remember, how this formula works, and various examples by using the TREND function to calculate the linear trend `on the basis of the specified linear data set. What is Trend Function in Excel?"The Trend is an inbuilt Statistical function in Excel that calculates the linear Trend on the basis of the specified linear data set. This function is used to calculate the predictive values of Y for a certain array value of X and uses the least square method on the basis of the given set of data series." The Trend function in Excel returns numbers in a linear trend matching known data points, that is, the existing data on which the trend forecasts the values of dependent Y (known y's parameter) on values of X (that needs to be present in linear data format). What is the least square method?Least Square Method is an useful Excel technique used in the regression analysis that searches the line of best fit (is a line through a scatter graph of data points that foremost indicates the relationship between those points) for a specified set of data, which helps to envision the connection between the different data points. How TREND function calculates linear trendlineThe Excel TREND Function finds the line that best fits your data using the leastsquares method (explained above). The equation for the line is given below: As per the least squares method, equation to represent one range of x values: As per least squares method, equation to represent multiple ranges of x values: Where: y  The axis y is dependent upon the variable the user wishes to calculate. x  The axis x is independent of the variable that the user is using to calculate y. a  It represents the intercept (specifies where the given line intersects the yaxis and is equal to the value of y where the value of axis x is 0). b  It represents the slope (specifies the steepness of the line). SyntaxParameterKnown_y's (required)  This argument represents a given set of the dependent data values of the yaxis that the user already knows. Known_x's (optional)  This argument accepts one or more sets of the independent xvalues.
New_x's (optional)  This argument represents one or more new xvalues for which the user wants to calculate the trend.
Const (optional)  This parameter represents a logical value specifying how the constant value in the equation y = bx + a should be computed.
Return TypeThe Trend function in Excel returns numbers in a linear trend matching based on the specified linear data set. #Trend Example 1: Predict the GPA for given values using TREND function.In this example, as you can see in the below table, we are given data for different test scores with their GPA. Predict the GPA value for different data. We already have data in columns A and B of the above table. We will interpret the existing values of GPA as the Y values, and their corresponding text scores are the known values of Y. We already have some values for X values as a score. Below we are given some new text scores for which we have to calculate the new GPA based on the existing values. Predict the GPA values We will use the inbuilt Trend function to predict the above GPA values. Follow the belowgiven steps to compute the GPA for the above series of data flows using the Excel TREND () function: Step 1: Select a cell to calculate TREND Place your mouse cursor to a cell from where you want to have the TREND() function result. Since we have to look for the GPA for cell E2, we have selected cell E2 to type our function. Refer to the given below image: STEP 2: Type the TREND function In E6, we will start typing the function with the equal to (=) sign followed by the TREND function. The function becomes: = TREND( STEP 3: Insert all the parameters
NOTE: We have used absolute reference for the above range of values because we want to keep them fixed while using the formula for the next cells.
NOTE: Since, we want to keep this value fixed, we have used the absolute reference for this parameter as well
Step 4: The TREND function will return the output As a result, the TREND function will return the predicted GPA for the given new text score on the basis of the earlier patterns of X and y data ranges. Step 5: Drag and Repeat the function to other cells Select the formula cell and drag the cursor towards the right corner of the rectangular box. You will notice that your cursor will change into a plus (+) icon. Drag the icon to the below two cells, and the formula will be replicated to all the cells changing the respective values accordingly where the absolute references will remain fixed. You will have the following output. So, using the Excel TREND function, we predicted the three values of Y for the specified new test scores. #TREND Example 2: Analyse the given data for a sequential period of time, spot the trend.Let's solve another example so you can understand the behavior Trend function, and then this function might seem less complicated to you. In this example, we are given the month numbers (representing the xvalues) in column A and sales numbers (representing the dependent yvalues) in column B. Based on this data, we will find the overall trend in the time series overlooking hills and valleys. We will use the inbuilt Trend function to spot the trend. Follow the belowgiven steps to compute the GPA for the above series of data flows using the Excel TREND () function: Step 1: Select a cell to calculate TREND Place your mouse cursor to a cell from where you want to have the TREND() function result. Since we have to look for the GPA for cell E2, we have selected cell E2 to type our function. Refer to the given below image: STEP 2: Type the TREND function In E6, we will start typing the function with the equal to (=) sign followed by the TREND function. The function becomes: = TREND( STEP 3: Insert all the parameters
NOTE: We have used absolute reference for the above range of values because we want to keep them fixed while using the formula for the next cells.
NOTE: Since, we want to keep this value fixed, we have used the absolute reference for this parameter as well
Step 4: The TREND function will return the output As a result, the TREND function will return the predicted GPA for the given new text score on the basis of the earlier patterns of X and y data ranges. Step 5: Drag and Repeat the function to other cells Select the formula cell and drag the cursor towards the right corner of the rectangular box. You will notice that your cursor will change into a plus (+) icon. Drag the icon to the below two cells, and the formula will be replicated to all the cells changing the respective values accordingly where the absolute references will remain fixed. You will have the following output.
Next TopicExcel Forecast Function
