introducing data through Microsoft Excel

Need this by tomorrow 

Page 1 of 5

CE 331L –Fluid Mechanics and Hydraulics Laboratory

Assignment 1: Presenting Experimental Data in Word Document

Assigned: (08/

2

4/2020 Sec. 1) & (08/27/2020 Sec. 2)
Due: (08/31/2020 Sec. 1) & (09/03/2020 Sec. 2)

Instructions: Type tables 1 and 2 in Word and create four graphs in Excel using the data from

these tables. Follow every step and create complete, aesthetically pleasing graphs. Make sure

that the graphs contain titles, axis lines, axis labels including units, and ensure that a best-fit

trend line (model) is fitted through the data which must also include an equation and

coefficient of determination (R2). Once you have added all required elements to each graph,

and you are satisfied with the appearance of the graphs, import them into a new Word

document. Then read carefully the questions given in step 5 and think critically using your

calculus knowledge to answer them.

Table 1. Linear Series

Time, t (s) 0 5 10 15 20 25 30 35 40

Velocity, V (m/s) 24 33 62 77 105 123 151 170 188

Table 2. Power Series

Time, t Distance, d
(s) (m)

0 0
1 4.9
2 19.6
3 44.1
4 78.4
5 122.5
6 176.4

1. Create a graph using the data provided in Table 1: Linear series.

a. Graph should show the data points on an x-y scatter plot chart type (with data

points shown as markers only, not lines). Choose Time as the independent

variable (x-axis) and Velocity as the dependent variable (y-axis).

b. Fit the data using a linear regression model (Under the Layout tab; Analysis sub

tab; and click on Trendline) and show the linear regression equation inside the

graph.

c. In the graph, also include the regression coefficient (or coefficient of

determination), R2 (Under the Layout tab; Analysis sub tab; Trendline; More

Page 2 of 5

Trendline Options). Make sure the equation and R2 is visible and not blocked by

data or legend.

2. Create a graph using the data provided in Table 2: Power series. Choose Time as the

independent variable (x-axis) and Distance as the dependent variable (y-axis).

a. Graph should contain the data points on an x-y scatter plot (with markers only).

b. Fit a regression model of the data. Choose the model (trendline) that best fits

the data: linear, power, polynomial, etc.

c. Show an equation of the best-fit model and corresponding R2 value inside the

graph.

3. Duplicate the completed graph from Step 2 Using log-transformed values. In your Excel

spreadsheet, add two additional columns to the right of Table 2. In the first added

column, calculate the logarithmic value of Time (s). In the second added column,

compute the logarithmic value of Distance (m).

a. Create a regular scatter plot (with axes in normal scale) by plotting the calculated

log-transformed values following the same steps as in the previous graphs. In

each graph add the best-fit model equation and its associated R2 value.

4. Duplicate the completed graph from Step 2 Using a log-log graph. Use the original

data from Table 2 (without logarithmic transformation). Create a scatter plot but this

time only change the x- and y-axis scales from regular to logarithmic scales. Just as you

performed in the previous steps, provide a best-fit model, regression equation and

associated R2 value. (Hint: The regression equation should read y = 4.9×2)

5. Answer the following questions:

a. For graphs from steps 3 and 4, are the slopes for the equations similar or

different, and why do you think this is so?

b. For graphs from steps 3 and 4, are the intercepts of the model equations similar

or different, and why do you think this is so?

Remember: This is not an actual laboratory experiment, so the report formatting does not

apply. Import your graphs into a Word document and make the graphs presentable; in black

and white and using Calibri font. Points will be deducted for graphs without proper formatting

or if they are missing any of the required elements (e.g., axis lines, axis labels and units, trend

lines, equations, R2 values, etc.). Turn in a printed Word document having 2 graphs per sheet

and one additional page answering the questions.

Page 3 of 5

6. Data Analysis and Statistics. Using the data from Table 3, determine the following with

use of Excel and using the equations provided (below the table):

a. Sample Mean for CBWS temperature dataset ( ix ) and SGWS dataset ( iy ).

b. Separate sample standard deviation (S) for each weather station’s data.

c. Slope and intercept of the linear regression comparing CBWS (X-axis) vs SGWS (Y-axis).

d. Coefficient of determination or regression coefficient (R2) for part (c).

e. Sum of squared error (SEE) for each weather station’s database.

Table 3. Average Ambient Temperatures.

Date
Caballo Weather Station

(CBWS)
Saltgrass Weather Station

(SGWS)

Temperature, Deg. C Temperature, Deg. C

9/1/2012 23.4 23.1
9/2/2012 24.1 23.7
9/3/2012 26.3 26.2
9/4/2012 26.1 25.5
9/5/2012 29.1 28.9
9/6/2012 25.6 25.8
9/7/2012 25.0 24.4
9/8/2012 17.0 16.9
9/9/2012 17.9 17.5
9/10/2012 21.1 20.8

Useful Equations:

Sample Mean:

�̅�

=

1

𝑛

∑ 𝑥𝑖

𝑛

𝑖=1

Standard Deviation:

𝑠 = (
1

𝑛 − 1
∑(𝑥𝑖 − �̅�

)

2

𝑛
𝑖=1
)

1/2

𝑜𝑟 √𝑠2

Page 4 of 5

Form of the Linear Regression Equation:

𝑦�̂� = 𝑚𝑥𝑖 + 𝑏

Where,

b= Intercept

m= Slope

Slope:





==

===

−











−

=

n

i

i
n
i
i
n
i

ii

n
i
i
n
i
i

xnx

yxnyx

m

1
2
2
1

1

11

Intercept:

n

xmy

b

n
i
n
i

ii 
= =

−

= 1 1
)(

Coefficient of Determination (R2):

Sum of Squared Error (SSE):





















−















−









−

=



 

====

= ==

2
11
2
2
11
2
2

1 112

n
i
i
n
i
i
n
i
i
n
i
i
n
i
n
i
i
n
i

iii

yynxxn

yxyxn

r

( )
=

−=
n

i

ii
yySSE

1
2

Page 5 of 5

Mean Squared Error (MSE):

7. Create a graph using the data provided in Table 3:

a. Graph should show the data points on an x-y scatter plot chart type (with data

points shown as markers only, not lines). Choose CBWS as the independent

variable (x-axis) and SGWS as the dependent variable (y-axis).

b. Provide a fitted linear regression model of the data (Under the Layout tab;

Analysis sub tab; and click on Trendline) and show the linear regression equation

inside the graph.

c. In the graph, also include the regression coefficient, R2 (Under the Layout tab;

Analysis sub tab; Trendline; More Trendline Options). Provide the R2 value next

to, or below the linear equation.

Compare the regression coefficient you calculated to the one in the graph. How do they

compare?

( )

n

yy

MSE

n
i

ii
=

−

= 1

2