Before you start the tasks, please press the 'Windows' key on your
keyboard and type 'matlab', then press enter to check if you have
matlab installed (you will use it on Monday).
Task 1: Import data, make into a table, do sorting on different
Open a new excel
worksheet and import the 'census_data.csv'
data file into your excel worksheet.
Insert a 'pie chart' to plot the population of all the states (do not
include the pop of the whole country).
Insert a 'Histogram' into the worksheet to plot the data in a different
a two-level sorting, first on the region, then on the state name:
Select all the data in the table, custom the sorting:
Add a sorting category, and change the sorting conditions:
Copy two columns into a new worksheet:
Highlight the two clumns, and click Insert - Table
Compute the percentage of the each region's population in the country.
Change the decimal number to a percentage by Right click the cell,
Format cells, then do the following:
Calculate the percentage for the other 3 regions using the same method.
display the region names and percent populations as a pie chart. Make
sure you can tell which region goes with which part of the pie.
(This is actually part of HW5)
Task 2: Conditional Formatting
The fabrication target
is 0.2500 inches, however, the high and the low tolerances are 0.0010
and -0.0005, which means all the products in the range of
(0.2500-0.0005, 0.2500+0.0010) will be accepted, otherwise, you should
report the result to be 'Reject High' or 'Reject Low'.
Here are these measured diameters of the products; 0.2501, 0.2496,
0.2498, 0.2512, 0.2502, 0.2512. Type these data into a table, and use
conditional formatting to add 'Accept', 'Reject High', or 'Reject Low'
depends on the results. The steps to do this:
Select the first cell you want to fill, then type the 'IF Statement' in
the cell, then autofill others.
Now, let's add some background colors to the cells. Select
the first 'ACCEPT' cell.
'Conditional Formatting', 'New Rules':
Use 'Format only cells that contain', 'Specific text',
You should see this:
Then drag the right bottom corner, autofill all the cells with the same
Now let's add more conditions. Select the first cell again, and then do
You will see this finally:
Feel free to use your perferred color scheme for this task. The bottom
line is you should have the font and the background color changed.
Now, let's do some statistics about the product quality.
Create a table beside the data:
Select the Row of 'Accepted', and the column of 'Number' in the table
you created, and type the function as shown below:
Then press 'enter', you should have the number of 'accepted' cases
reported in that cell.
Do the similar thing to report the number of all the other cases. The
function used here is: COUNT(range), which will return the 'counts'
(how many cells) of the selected range.
Do the similar thing for all other percentages. If the result you got
is a decimal number, 'Right Click' the cell, 'Format Cells', change the
format to be 'percentage'.
Finally, you should see this:
Complete all the tasks above in ONE excel file. Use different
worksheets to separate these tasks.
Send your excel file (along with your HW5) to firstname.lastname@example.org
For HW 5, the pie chart can be modified in the following way:
Click' the pie chart itself, select 'Add Data Labels', then 'Right
Click' the data labels just created and select 'Format Data Labels',
then check the options shown below: