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

Introduction to construction statistics using Excel Chapter 1 7

1.4 Using Excel to forecast boiler tube replacement
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 devi-
ations 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

Using Excel statistical functions set up Table 1.4.1 and Fig. 1.4.1
To use the Excel statistical functions, go to quick access toolbar, select
More Functions, and then, select Statistical.

Excel statistical functions are used to calculate values for data. Set up Tem-
plate 1.4.1.
To use COVAR (R1, R2), highlight the array of values for x (array R1),
enter a comma, and highlight the array of values y (array R2).
To use VARP (R2), highlight the array of values for x (array R2).
To use SLOPE (R1, R2), highlight the array of values for y (array R1), enter
a comma, and highlight the array of values x (array R2).
To use INTERCEPT (R1, R2), highlight the array of values for y (array
R1), enter a comma, and highlight the array of values x (array R2).