Peeking into computer science lab manual

This language is called computer code. In the exercise, students will follow an algorithm to draw an image A central computer, or 'server', acts as the storage location for files and applications shared on the network. Computer Basics Page 2 of 2 From reading the information on the previous page, answer the following questions. Select an item that would be used as Home Buy Worksheets!! Handwriting Worksheets 10 Handwriting Worksheets — How is my handwriting? Handwriting Worksheets — Complete Collection.

Chapter One Introduction to Computer Introduction to Computer Computer A computer is an electronic device, operating under the control of instructions stored in its own memory that can accept data input , process the data according to specified rules, produce information output , and store the information for future use1.

You can also filter column and row labels in the same way. Excel pivot tables allow for different calculations of the data value.

Currently, the Expenses are being summed up. Let us modify this so that the table shows the average of all expenses. In the pivot table field list to the right of the screen , click on Sum of Expenses and select Value Field settings from the menu. The dialog box allows you to select the calculation type for the data field.

Select Average and then press the OK button. Exercise 3 Modify the pivot table so that it shows the number of times expenses incurred for each month, per department and per employee, for all expense types. Calculate the totals for each column and row hint: try dragging the formula. Make sure all your data shows the separator mark. Create a meaningful chart out of this table. Make sure you include titles and data labels as appropriate. Modify both the chart and plot area backgrounds to colors of your choice.

Create a pivot table that shows the total amounts spent on groceries and clothes, by date and store. Exercise 4 Go to the exercise4 sheet in your workbook. The data in the table shows the population in Canada by age and sex group for Lab 3: Conditionals and Lookup Tables Conditional functions One very useful set of built-in functions in Excel is conditional functions. As the name implies, these perform certain operations based when a specified condition is satisfied.

This is best illustrated through an example. Open excellab3. Now, let us use Excel to give us a count of the number of employees in each department. The range is the data values you would like the count to be performed on, and the criteria are the conditions that need to be satisfied for a cell to be included in the count. Let us count the number of employees in the IT department. Note that IT was placed between quotation marks, as should any criteria you enter.

When you press Enter, the result of the formula is shown. Exercise 1 a Calculate the number of employees in the Accounting department. Remember to use cell addresses.

Hint: use two countif operations. For instance, let us calculate the sum of all salaries greater than Now let us try using a cell address as a criterion. We would like to find the number of employees who have a salary greater than Reagans salary.

The only difference here is that to include a cell address in the criterion; there is a special syntax as shown below. SUMIF also lets you define separate ranges for the criterion and the summation. For example, we might want to calculate the sum of all salaries of the IT department. As you can see above, we first provide the formula with the range of values on which the criteria will be applied. We then enter the criteria, followed by the range which is to be summed up if the criteria apply to the 1st range.

Exercise 3 a Calculate the sum of all salaries that are below the average of salaries. IF functions IF functions or statements allow you to ask the question is this true or false? It then allows you to implement different actions based on the outcome. That is, it allows you to compute two different outcomes based on whether a certain criterion logical test is true or false. In excellab3. Let us use IF statements to display which students have passed, and which have failed.

First, start with the logical condition that needs to be evaluated. In this case, we want Excel to display Passed. Finally, enter the value if the condition is false.

The IF function shown below reads: if B2 is greater or equal to 60, then display Passed; otherwise, display Failed. This requires the use of nested IF statements. Nested if statements allow you to embed IF statements in other IF statements, capturing more complex scenarios. Nested IF statements are done by replacing the value if false or the value if true in the function with a new IF statement.

Exercise 4 The instructor for this course would like to reward the students who got a grade strictly above Use if statements to display Reward or No Reward beside each student. Notice that the logical condition of the IF function is similar to what we had done before: we require the grade cell to be greater than a certain value.

If this condition is false, or B2 is less than or equal to 90, we need another IF condition in order to determine the letter grade since values that are less than or equal to 90 can result in different letter marks. For example, 77 is a B, 65 is a C, and 20 is F. All these values are less than or equal to Notice that with each new IF statement, we opened up new parenthesis and that at the end we had to close all of them.

Drag the IF statement into the rest of the column. Exercise 5 Open the if exercise sheet. The sheet shows the names of salespeople along with the revenues they have brought to the company. If not, we need to check the mark if it is a C or an F. Lookup functions You may have noticed that some of these if statements can get quite long. The first thing you need to do is to create a lookup table that displays which numerical grades correspond to which letter grades.

Notice that the lookup table is in ascending order by the first column. The way this table is interpreted is that we are providing Excel with the minimum numerical grade for each letter grade. For instance, the F grade is between 0 and 50 since a D starts at 50 , not including The A grade is anything greater than or equal to If we had not set the minimum numerical grade for F as 0, any value below 50 would return an error.

As you can see, we received plenty of error values. Double-clicking one of those values will show you the problem. When we dragged the formula, the address of the lookup table was incremented. The first parameter we give the lookup function is the value to be looked up, which in this case, the numerical grade. The second input is the lookup table. Now drag the function to the rest of the column. When the formula is copied, Excel does not change absolute addresses. To the contrary, the reference D2 is called a relative address.

In essence, it is saying lookup the value immediately to the left of cell E2 where the lookup function is created and go from there.

So, when the formula is copied from E2 to E3, D2 will be updated to D3. Lookup tables should be always referenced in an absolute way. A shortcut to do this is by clicking on F4 right after selecting the lookup table. Now try dragging the formula again. Frequency THz In the lookup exercise sheet, fill in the colors corresponding to the given frequencies using the lookup function. Remember: - The lookup table must be in ascending order.

Lab 4: Conditional Formatting and Lists Conditional formatting Conditional formatting allows you to make data trends stand out visually. Open the file excellab4. Let us say we would like to quickly view who has OT Over Time hours more than 10 hours.

In the Home tab, click on Conditional Formatting in the Styles group. In the dialog box that pops up, Enter Notice that you can modify the way that the cells are formatted by choosing the fill and text colors from the drop down list labeled with. For this exercise, leave the formatting as light red for the fill and dark red for the text. Click the OK button when you are done. Other conditional formatting features allow you to get a quick overview of the values of all cells in a range.

One of them is data bars. Data bars assign the longest bar to the largest value in a list and the shortest bar to the smallest value. The values in between are then given bars according to their respective values. Let us apply this to the Total column.

Select the range of cells under the Total heading. Click on the Conditional Formatting button and navigate to the Data Bars menu. Select the Purple Data Bar button. Using color scales also gives you a similar result as data bars. Both features allow you to get a quick view of how your data values compare to one another.

The 2-color scales assign one color to the maximum. The values in between get assigned in-between colors. In addition, the 3-color scales assign a color to a selected midpoint.

The colors and values assigned to them can be modified by selecting Manage Rules under the Conditional Formatting header. Exercise 2 After you have cleared the data bars rule from the Total column, apply the Green-Yellow-Red color scale. Exercise 3 Edit this rule so that the maximum value is shown in a pretty sky blue color. The final aspect of conditional formatting we will cover is icon sets. When using icon sets, each cell is provided with an icon depending on its value. There are no gradients in icon sets.

Let us format the Rate column using icon sets. Select the cells in the Rate column and apply the 3 traffic lights rimmed formatting to them, as shown below.

You can also delete rules by selecting the cells where you would like to clear the rules, then navigating to the Clear Rules menu under Conditional Formatting. Click on Clear Rules from Selected Cells.

If you want to change the way that the icons are assigned, you need to edit the rule Manage Rules. What this.

In this case, the minimum value associated with 67 percent is 0. This is What you need to understand about this is that percentages are calculated relative to the lowest and highest values when percentiles are calculated based on the position of a value in the ordered list of values. Exercise 4 Format the OT Rate column using the 3 flags icon style. Percentiles do not calculate the percentages within ranges of values. Percentiles are based on the order of a value among the other values.

If you are interested in the Math, this is because 0. Sorting lists Sorting a list or sorting an entire table are both functions that will most likely come up whenever you are doing data analysis.

You can easily sort data in alphabetic, numeric, or even chronological order. Let us try sorting our table by the ascending alphabetic order of the names of the employees. Select the range of names in the table. What this message is telling us is that with the current selection, only the names column will be sorted. The data in the rest of the table will not be sorted.

This is why we need to select the Expand the selection radio button. This will allow the entire table to be sorted according the alphabetic order of the names. Press the Sort button. The entire table has now been sorted. Excel also lets us perform more complicated sorting.

Let us try to sort the table by the cell icons of the Rate column. Under Sort and Filter, select Custom Sort. This dialog box shows the column headers in our table, how we want to sort them, and what order we want them in. Select Rate as the Sort by column, and then select for it to be sorted on Cell Icon. The order menu now shows the three icons used in the Rate column.

Let us ask for the green icon to be placed on top, as shown below. Now we can add another level of sorting so that the red icon is at the bottom. Click on the Add Level button in the dialog box. Select Rate and Cell Icon again. This time, make sure the red icon is ordered on the bottom, and then click the OK button. Exercise 6 Use custom sort to sort the table in the descending order of the OT Rate and then in ascending order of the names of the employees.

Filtering lists Filtering in Excel allows you to show only the rows of data you are concerned with, and hide all other data temporarily. Open the filters sheet in excellab4. One obvious use for filters here is to view the sales information of only a few regions of interest. Let us see how this can be done. Start by selecting the Region column. Select Filter from the menu. Notice that there is now an arrow next to the Region header.

This arrow means that a filter can be applied to this column, but no filters are currently being used. Click the arrow next to the Region header. Let us only show the data for sales in the East region. One way to do this is to deselect NW and SW as shown below. Now, only the rows showing information from either the NE or SE are shown.

Notice the button next to the region header has changed to a filter icon indicating that a filter has been applied. There is another way by which the same result can be achieved. First let us remove the applied filter. Click on the button next to Region and select Select All. Click the OK button.

Now that we can see our original data, let us add a new filter. Select the arrow next to Region and move the mouse cursor over Text Filters. Choose Ends With. Now let us remove the filter by clicking on the button next to Region and selecting Clear Filter from Region from the menu. In the dialog box that pops up, enter E as shown below. This will only show the rows in which the Region ends with an E. Click on the OK button. Exercise 8 Remove the previous filters from the table.

Show only the rows from the months April, May, and June of all the years 2nd quarter. From those rows, only show the ones with Sales above the average. Exercise 7 Create a new filter that only shows the data for salespeople who have joined after May , and before or during May Lab 5: Drop-Down Lists and Dynamic Charts Drop-down lists Drop-down lists are useful in making data entry easier and faster by limiting the number of options to pre-defined items.

Open the file excellab5. The sheet shows a partial table of students in a class. We would like to limit the possible valid entries into the major column. Nex,t we need to name our list. Select the list of majors that was created. Go to the Name Box at the left of the formula bar shown in a red square below and type in an easy to remember, descriptive name of the list, such as ValidMajors.

First, we need to create a list of all valid majors that will be accepted. Here are a few that one can think of. Feel free to add your own major if it is not in the list. To create the drop-down list, first select the range in which the list will be used see the illustration below.

Go to the Settings tab in the dialog box that pops up. In the Allow box, select List. We need to pick the data source for our list. We can choose whether we want to restrict the users entries to the list, displaying an error message on any other entry.

To do this, go to the Error Alert tab. Excel lets you pick the style of the error message, as well as the message title and body. You can also ask Excel to show an input message describing to users what they need to do.

Go to the Input Message tab and enter an appropriate title and input message:. Exercise 1 Create a drop-down list for the Gender field. The list should show a warning message on invalid data entry. No input message is necessary. Now when you click on a cell in the Major column, you get an arrow indicating a drop-down list is available, as well as the input message we had provided. Try entering a major that is not on the list. You will see an error message. Exercise 2 Create a drop-down list freshman, sophomore, etc.

The list should not allow blank entries. OFFSET function The OFFSET function allows us to start somewhere in the spreadsheet, move in any direction in order to locate a cell or a range of adjacent cells, and return the value of this cell or a set of values for the range of cells. Reference: is the address of a cell or a range of adjacent cells Rows: is the number of rows to move from the reference.

A positive number means moving down, and a negative number means moving up from the reference. Cols: is the number of columns to move from the reference. A positive number means moving right, and a negative number means moving left from the reference. Height: is an optional positive value indicating the number of data rows to be returned by the function.

Width: is an optional positive value indicating the number of data columns to be returned by the function. Open the dynCharts1 sheet in excellab5. The value returned is -6 since the function reads as follows: move one row down first 1 from cell A2 and one column right second 1 of cell A2, and return a single cell. A height and width of 1 third and fourth 1s, respectively indicate it is a single cell that we are offsetting to.

This is cell B3, which contains 6, the returned value. When both the height and width are 1s, these arguments can be simply left out. If you attempt to use it on its own, you will get an error message. B2 is the top left corner of the required range of values. The range of cells from B2 spans 5 rows and 1 column. Recall that height and width must always be positive. Here we are adding all the temperature values together. Starting at cell B5, move 3 rows up and 1 column left. The size of the returned data is also one cell since height and width are omitted.

The offset is A2 and the value returned by this function is Jan. Now what if we want this SUM value to be updated whenever we add a new months value? However, this is cumbersome. Our new formula is:. That is, the top left corner of the required range of cells is B2. B2 contains the first temperature value. Remember that the column title Average Temperature 0C is a non-empty value, but it should not contribute to the number of values we are trying to sum up.

The returned value of the SUM function is 0, the 5 temperatures in column B cancel each other. Dynamic charts In this section, we will take a look at how to make a chart that dynamically updates its values. Start by deleting the SUM formula that was created previously.

Remember how we named a range of cells in the drop-down sheet? This time we will be naming formulas. Enter the name, Months. In Refers to, type in the formula that refers to the Months column as shown below. Type carefully. If you want to check if the range you entered is correct, click in the comments field then back in the Refers to field. The range should be highlighted in your worksheet.

When you are done, press OK. Note that a cell address can be prefexed by the sheet name in which the cell is contained followed by the exclamation mark! For instance. Exercise 3 Create a Name Temps for the temperature column. If you need to view or modify the names in your worksheet, click on the Name Manager in the Defined Names group. Now that our formulas are ready, we can create the chart. In the Insert tab, under the Charts group, select 2D line. Make sure no cells are selected while you are doing this.

A blank chart is created. Rightclick the chart and click on Select Data. We now need to decide which column we would like to see on the vertical axis and which we would like on the horizontal one. Let us place the temperatures in the vertical axis. The Series name is the name of the vertical y axis values.

In this case, it is cell B1. The series values are the Temps formula we created. To refer to this formula, you must first type in the sheet name it was created in, followed by an exclamation mark. Otherwise, Excel will not know where to find this name. When you are done, press the OK button. Your chart should look like this:. Note that in the dynamic chart we created, because the average temperatures for some months are negative and the x-axis crosses y-axis at the temperature of 0o, the x-axis appears in the middle of the chart.

If you want to move the position of the x-axis, open the Format Axis dialog by right-clicking on the y-axis as shown below. In the Format Axis dialog box that appears, under Horizontal axis crosses, enter Axis value to be the lowest temperature value along the y-axis, which is Press the Close button when you are done.

Exercise 4 Sheet dynChartsEx shows a table that shows where money is being spent. Create a pie chart which shows how the money is spent. The chart should be updated automatically whenever a new expense is added. Lab 6: Interactive Content Using combo boxes to create dynamic charts Using combo boxes, we can allow users to select which data they would like to see on a chart.