Excel how to–Trend function

Excel can calculate trend for set of values using least square line method.
Least Square line method – Uses a straight line y=a+bx.
It require two data set x and y , where y is dependent variable and x is independent variable.


TREND(known_y’s_value, known_x’s_value, new_x’s_value, Const)

Const is a logical value. It is set to 1 if Const=TRUE ( Default Value) or set to 0 if Const=FALSE

How  to use Trend

In our example a product is tested for defect. Product range having 2 or less defect per hundred is given 9 point and 5 or more defect per hundred is given 2 points. Defect between 2 and 5 are awarded points linearly.trend 
Now we have inserted formula in Cell C4. Now this formula will calculate points for different number of defects(TREND).

Download above trend analysis workbook click here