Engineering Data Analysis 2

Exercise No.
1
Open the spreadsheet Data Analysis_Exercises.xlsx (which can be found under the Excel
section on the Library Training Resources page. The External Data Link sheet is selected.
Importing Data from websites
Data from websites and other sources can be imported into Excel if it is in an appropriate
format.
1. Copy the URL of the web page with the data you
want to import.
e.g. World University Rankings on Wikipedia (which can be found
in cell A1 of the External Data Link sheet)
https://en.wikipedia.org/wiki/QS_World_University_Rankings
Note: For this exercise ignore From Web in the Get

External Data group. It will bring in the entire web
page and not just a selected table
2. Navigate to the Data tab
3. Click on New Query (in the Get & Transform
group)
4. From the drop down menu, select From Other
Sources > From Web
This opens the dialogue box for you to enter the URL of the web
page with the data you want to import
5. Paste the URL in the From Web dialogue box and

click OK
The Navigator Pane will open with a list of data that can be
imported into excel
6. Select the required data set (QS World University
Rankings – Top 50) on the left pane of the
Navigator to preview it
NB: You can use the edit button to clean the data before importing
7. Select QS World University Rankings – Top 50

8. Click on Load
A connection will be created to the data on the website. This will

ensure that refreshing your excel file will update the data to the
latest version. Excel will then open a new worksheet with the
imported data.
Refresh Linked Data

9. Click on any cell within the data table
10. Click on the Data tab
11. Select Refresh All
NB: Refresh all will refresh all connections in the workbook. If you want
to refresh data on a single sheet click Refresh
NB: You may get a Microsoft Excel Security Notice about connections
to external data sources. You can safely click OK here but see the
section on Considerations when importing data into Excel below
for further information.
Exercise No. 2
Open exercise files and enable content
1. Open the exercise file Data
Analysis_Exercises.xlsx and select the Importing Data & Histograms
worksheet.
2. Click on the Enable Content button on the
Security Warning (if necessary)
3. If you get a Security Warning dialog box.
Click on Yes
Note: In Office 365 (Windows version) Microsoft removed the Text Import Wizard as an
option when using steps below. They force you to use the Power Query window which does
not have the “Treat consecutive delimiters as one” option. You can get around this by
opening the text file directly in Excel which will launch the wizard below
Import data from text file:
4. Click the Data tab
5. Click From Text (in the Get External Data
group)
6. Locate data_analysis.txt
7. Click on Import (in Mac – Get Data)
8. Click on Delimited option
9. Click Next
10. Tick the following options:
Tab
Space
Treat consecutive delimiters as one
11. Click Next
12. Ensure General option is selected

13. Click Finish
14. Assign data to $A$1 in existing worksheet

15. Click OK
Exercise No. 3
Mac users may need to add the Analysis Tool Pack
Data Tab – Far right hand side – click Analysis

Tools button
Click next to Analysis ToolPak

Choose OK
The Data Analysis button will now be visible
1. Click Data Analysis (at the far right of ribbon)

on the Data tab
2. Click Descriptive Statistics

3. Click OK
4. Highlight cells $A$1:$D$201 for Input Range
5. Select Grouped by columns
6. Click Labels in first row box
7. Click Output Range
8. Highlight cell $G$1 for Output Range
9. Select Summary statistics
10. Click OK
NB: To obtain descriptive statistics for one group
ensure that only one column is selected.
Exercise No. 4
To use Basic Statistical Functions
1. Ensure you are on the Basic
Statistics worksheet
2. Select the Home tab
3. Click in cell C14
4. Click AutoSum
Check the range is (C5:C11)
5. Press Enter
6. Use Autofill to calculate sum for

remaining weeks)
7. Calculate with statistical functions

Sample size = COUNT
Mean = AVERAGE
Minimum value = MIN
Maximum value = MAX
Note: Mean and Average are different terms for the
same thing when dealing with Statistics
8. Select cells C14 to C18
9. Autofill across to fill cells in
remaining weeks
NB: For quick statistical reference refer to status bar after highlighting a selection of values. Adjust options on
status bar by right clicking on it and selecting items.
Exercise No. 5
To use Variance Function on a sample
2. Click button in formula bar

3. Change category to Statistical
4. Click on VAR.S function
5. Select range (C5:C11)
6. Click on OK
To use Standard Deviation Function on a sample

2. Click button in formula bar

3. Change category to Statistical
4. Click on STDEV.S function
5. Select range (C5:C11)

6. Click on OK
Repeat steps above for entire population using range
(C5:I11)
• Click cell C25: Overall Average:
=AVERAGE(C5:I11)
• Click cell C26: Overall Variance: =VAR.P(C5:I11)
• Click cell C27: Overall Std Deviation =
STDEV.P(C5:I11)
• Click cell C33: Overall Sum = SUM(C5:I11)
To find WeeklyTotal as a percentage of the Overall Total

1. Go to cell C34
2. Enter =C14/C33 in the formula bar
3. Press function key F4
Note: This will change cell reference C33 to absolute reference
$C$33
4. Press enter
5. Autofill across (D34:I34)
Exercise No. 6
Use worksheet “Importing Data & Histograms”
Using the tool in Data Analysis
Prepare data for a histogram of weights
1. Go to cell F19
2. Type “Bin”
3. Go to cell F20 4. Type 0
5. Go to cell F21
6. Type 50
7. Select F20 and F21
8. Autofill to display a value of 500 in cell F30
Input Range: This is the data that you want to analyse by using the Histogram tool.
Bin Range: This represents the intervals that you want the Histogram tool to use for
measuring the input data in the data analysis.
9. Click Data Analysis (at the far right of the
ribbon) on Data tab
10. Click on Histogram

11. Click OK
Complete the dialog box as follows:

• Input Range = $A1:$A201
• Bin Range = $F$19:$F$30
• Tick Labels
• Output Range: $I$21
• Tick Chart Output
12. Click OK
To display the frequencies in Histogram:

1. Click on Histogram in worksheet
2. Click Data Labels on Add Chart Element
button
3. Select Outside End
NB: Table with Bin and Frequency headings will appear along with Histogram graph.
Resize graph as required.
Using the Statistics Chart - Histogram option

Select the data range A1:A201
Insert tab – Charts - Statistics Chart -
Histogram
A Histogram will appear
Windows:
Single click the X axis – Double click the X axis to launch the Format Axis
panel on the right of the screen.
Choose the Axis Option and expand the Axis Options
Set the Bin Width to 25
Set the Overflow bin to 200
Set the Underflow bin to 50
Mac:
Right mouse click the blue data series
columns
Choose Format Data Series…
Expand the Data Series Options (if necessary)
Change Bins – Auto to Bin Width Set the Bin Width to 25
Set the Overflow bin to 200
Set the Underflow bin to 50
6. Data and Results
7. Conclusion
9. Assessment Rubric:
10. References:
Excel Data Analysis Staff Training (Bookings). (n.d.).
https://web.library.uq.edu.au/files/142294/20210831_Excel_Data_Analysis.pdf

Engineering Data Analysis 2

Uploaded by

Copyright:

Available Formats

Engineering Data Analysis 2

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Engineering Data Analysis 2

Uploaded by

Copyright:

Available Formats

Exercise No.

Note: For this exercise ignore From Web in the Get

5. Paste the URL in the From Web dialogue box and

7. Select QS World University Rankings – Top 50

A connection will be created to the data on the website. This will

Refresh Linked Data

12. Ensure General option is selected

14. Assign data to $A$1 in existing worksheet

Data Tab – Far right hand side – click Analysis

Click next to Analysis ToolPak

The Data Analysis button will now be visible

1. Click Data Analysis (at the far right of ribbon)

2. Click Descriptive Statistics

6. Use Autofill to calculate sum for

7. Calculate with statistical functions

2. Click button in formula bar

To use Standard Deviation Function on a sample

2. Click button in formula bar

5. Select range (C5:C11)

To find WeeklyTotal as a percentage of the Overall Total

10. Click on Histogram

Complete the dialog box as follows:

To display the frequencies in Histogram:

Using the Statistics Chart - Histogram option

A Histogram will appear

You might also like