Page 297 - Industrial Process Plant Construction Estimating and Man Hour Analysis

Appendix C

Excel functions and

mathematical functions

Excel functions

Graphic analysis of data
Use Excel’s chart capabilities to plot the graphic straight line given by the
equation y¼a+bx
To use the Excel chart capabilities, highlight the range x:y, and select insert,
and select from Charts, Scatter; go to quick access bar, and select from Chart
Tools, design, and from Chart Layouts, select Layout 9.

Excel functions

Excel statistical functions for forecasting the value of y for any x. Thus, a and b
can be calculated in Excel. Where R1¼ the array of y values and R2¼the array
of x values
b¼SLOPE (R1, R2)¼COVAR (R1, R2)/VARP (R2)
a¼INTERCEPT (R1, R2)¼AVERAGE (R1) b * Average (R2)
SLOPE (R1, R2)¼slope of regression line
INTERCEPT (R1, R2)¼y-intercept of the regression line
FORECAST (x, R1, R2) calculates the predicted value of y for given value
of x. Thus,
FORECAST (x, R1, R2)¼a+b * x where a¼INTERCEPT (R1, R2) and
b¼SLOPE (R1, R2)
TREND (R1, R2)¼array function that produces an array of predicted y
values corresponding to x values stored in array R2, based on the regression line
calculated from x values stored in array R2 and y values stored in array R1.
COVAR (R1, R2)¼returns covariance, the average of the products of
deviations for each data point pair in two data cells
VARP (R2)¼calculates variance based on the entire population (ignores
logical values and text in the population)

277