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.
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