Lecture5_More_Excel_Functions

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).

1. Task 1: Import data, make into a table, do sorting on different categories.
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 manner.


Perform 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.

Then 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)

2. 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.
Go to 'Conditional Formatting', 'New Rules':


Use 'Format only cells that contain',  'Specific text', 'Containing', 'ACCEPT'.


You should see this:


Then drag the right bottom corner, autofill all the cells with the same 'Conditional Formatting'.


Now let's add more conditions. Select the first cell again, and then do this:


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 homeworkflc02@gmail.com

-------------------------------------
For HW 5, the pie chart can be modified in the following way:
'Right 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:


Due Wednesday