CS105 Lab 7 Excel

1
CS105 Lab 7Excel IF

• Announcements
• MP3 is out and is due on Tuesday, March 7th at
  8pm.
• Quiz 3 is online and is due on Saturday, March
  11th at 1159pm.
• Scores for MP1 and MP2 are posted.
• Feedback forms for Midterm 1 will be handed back
  in lab this week.
• You can view the Midterm 1 problems in Compass.

2
Lab Objectives

• Learn the IF function.
• Learn to use nested IF functions.
• Learn to reference other worksheets in Excel.
• Learn to use the Chart Wizard.
• Go to the course website and download the Excel
  Worksheet for Lab 7.
• http//www.cs.uiuc.edu/class/cs105

3
Scenario

• This spreadsheet contains sales data of a tea
  manufacturing company, which sells 3 different
  brands of tea.
• Lets find out how much of the revenue comes from
  each product line.
• Well use the IF function to do this.

4
The IF Function Flowchart
5
How does IF work in Excel?

• Recall from lecture how the formula
  IF(condition, result1, result2) works.
• Excel looks at the condition. If it is true,
  result1 is returned. If it is false, result2 is
  returned.
• The condition must be something that is either
  true or false that is, the condition must be
  BOOLEAN.

6
Setting up our IF function

• Our product names are in column A.
• If a cell in column A contains the same product
  name as cell E2, we want toput the revenue value
  (column D) in the Sales column for Lively Lemon
  Tea(column E). Otherwise we want to put 0 in
  that column.
• How can we do this with IF?

7
Flowchart for cell E3
Display 0
Product is Lively Lemon Tea
False
True
Display revenue
8
The Formula for Cell E3

• IF(A3E2,D3,0)
• Double-click on the fill handle to fill the
  column for Lively Lemon Tea.
• Use the fill handle to copy the formula to cells
  F3 and G3.
• Double-click on the fill handle in F3 and G3 to
  fill columns F and G.

9
Nested IF

• You can have an IF function inside another IF
  function.
• This is handy if you have more than one condition
  to test.

10
The Nested IF Flowchart
11
Calculating Eastern Region Sales

• We want to compute the total sales for each
  product in the East region.
• If Column C has the value East in it, then
  check the product name and fill in Columns H, I,
  J with the appropriate revenue from column D.
• Else, we want to fill in 0.

12
The Formula for Cell H3

• IF(C3East,
• IF(A3H2,D3,0),
• 0)
• Double-click on the fill handle to fill the
  column for Lively Lemon Tea.
• Use the fill handle to copy the formula to cells
  I3 and J3.
• Double-click on the fill handle to fill columns I
  and J.

13
AND, OR, and NOT in Excel

• As in SQL, Excel has the boolean functions AND,
  OR, and NOT.
• AND(param1, param2). Both param1 and param2
  must be TRUE for the function to return TRUE.
• OR(param1, param2). One or both of param1 and
  param2 must be TRUE for the function to return
  TRUE.
• NOT(param). Returns the logical opposite of
  param.

14
Using AND to check two conditions

• There is an alternative way to write the IF
  statement in cell H3. We want the revenue to be
  displayed in H3 if the product is Lively Lemon
  Tea AND the region is East

IF(AND(C3"East",A3H2),D3,0)
15
Now lets get some totals

• Go to the Statistics worksheet.
• In cell A8, put a formula to add up the range
  E3E26 of the Tea Data worksheet. The formula
  is
• SUM(Tea Data!E3E26)
• Use the fill handle to copy the formula to B8 and
  C8.
• Compute the grand total of tea sales in cell D8

16
Making a Chart

• Select cells A7C8.
• From the Insert Menu, select Chart.
• For "Chart Type", select "Pie."  Click "Next.
• Click Next again.
• Click on the Show Percent button in the Data
  labels section. Click Next.
• Select the option to place the chart as an object
  in the Statistics worksheet.  Click "Finish.
• Move the chart to a convenient place in the
  Statistics worksheet.

17
Final Result

• We can change this pie chart by right clicking on
  the chart area and choosing an appropriate option.

18
Getting Legend Data Labels
In our case, setting the legends category labels
was done automatically. But sometimes, you
change this manually. Right-click on your chart.
Select Source Data. Click on the Series tab.
You can set the range from which to choose labels
in the Category field.