The TREND() function returns values that fit a calculated trend in the known data values. The syntax for the function is TREND(known_y's, known_x's, new_x's, const). The arguments "known_y's" and "known_x's" can be an array of numerical values, references to a range of cells or a named range of which you want to find the square of the correlation coefficient. If the "known_y" and "known_x" contain a different number of data points and #N/A error will be returned. The "new_x's" array is a list of x values for which you require the corresponding y values and the "const" is a logical value that determines the existence of b (TRUE or omitted calculates b as part of the result, FALSE sets the value of b to zero). The trend function uses the known values of x and y and the assumed existence of be to calculate a trend line that best describes the data. The trend line of the form y = mx +b is then used along with the new x values to calculate the values of the unknown y data points fitting them on to the trend line. For example: TREND({2,4,6,8,10},{4.5,8.9,13.3,17.7,22.1},13,TRUE) = 28.7 as the trend line is calculated as y = 2x + 0.1 which yields the y values of 28.7 when the x value of 13 is substituted in the equation which is equivalent to extrapolating the value. [chart] The TREND function can also be used to interpolate, that is to find corresponding y values that lie on the trend line for values of x that are between known values. e.g. TREND({2,4,6,8,10},{4.5,8.9,13.3,17.7,22.1},5,TRUE) = 10.6. The TREND function uses the known values of x and y as a basis to calculated the trend line and then substitutes the known x value are returns the desired y value. i.e. y = 2*(5) +0.1 = 10.6. [chart] To learn more about trend lines or "lines of best fit" in mathematics see: [The Lines Of Best Fit Mathematics Knowledgebase]. [The LINEST() function knowledgebase] [The PEARSON() function knowledgebase] [The RSQ() function knowledgebase] Note: To enter this function in more than one cell and get results for ALL the "new_x's" the function must be entered into the cells as an array so that the results are returned as an array of values. e.g. TREND({2,4,6,8,10},{4.5,8.9,13.3,17.7,22.1},{11,12,13},TRUE) = {24.1, 26.5, 28.7} [chart]If the formula is not entered as an array formula it will return only single results.

How to use the TREND() function: Type " =TREND( ". Enter the reference for the "known_y" values "A2:A10". Type a comma. Enter the reference for the "known_x" values "B2:B10 ". Type a comma. Enter the references for the "new_x's" values "C2:C10". Type a comma. Enter the value of the "constant". Type")" then press the "Enter" key. Select the range C11:C14. Press the "F2" key the press the Ctrl + Shift + Enter keys.

When there are no unknown values of x entered into the function it simply returns the predicted y values for the known values of x according to the trend line calculation. For example this data set has a high linear correlation coefficient but it is mot equal to 1. so TREND({ 2.1,3.9,6.3},{4.8,8.8,13.0}) = {4.98,8.47,13.13}. The values represent the corresponding y values that would be if the y values where on the trend line. Note: If the arrays contain elements that cannot be translated into numeric values the function will ignore those elements of the array.