last week lightening review copyright © 2008 pearson prentice hall. all rights reserved. 1 formulas...

Copyright © 2008 Pearson Prentice Hall. All rights reserved.

Last Week Lightening Review

1

Formulas – A formula of sorts, usually math based, which always starts with an “=“ (equal sign) (e.g. =A1+B2))

Functions -- A pre-constructed formula that makes difficult computations less complicated (e.g. =SUM(A1:B2))

Logical Functions -- Return an answer when a particular condition is true.

IF(logical_test, value_if_true, value_if_false)

IF FunctionFormat:

=IF(logical_test, value_if_true, value_if_false)

Example (would output “5 Cats!”):=IF(B2=5,“5 Cats!”,“Not 5 Cats!”)


VLOOKUP Review

3

Allows for lookup within a vertical table of information

Well suited for large tables of data, such as tax tables


VLOOKUP Review

4

Has three arguments:a lookup value stored in a cella range of cells containing a lookup tablethe number of the column within the lookup

table that contains the value to return

VLOOKUP(lookup_value, table_array, col_index_num)


VLOOKUP Example

5

“Vertical Table”


VLOOKUP Example

6


VLOOKUP Lab

7

Grab the file from here:http://users.edinboro.edu/nsullivan

Click on our classGo to the bottomSelect “Class 9 Lab” link

http://users.edinboro.edu/nsullivan


Questions on VLOOKUP?

8


Excel Exam – Next Week

9

Format similar to Word ExamTimed Test: 1.5 hours to complete it.

Things to know:General formatting (bold, italics, cell shading,

etc. etc.)Functions:

Average, Count, IF, VLOOKUP, MINIMUM, MAXIMUM, etc.

How to Chart


Homework – Excel Topic 3

10

Due on 11/5anything past 11/5 is LATE and will be

dropped 1 letter grade.

Will NOT accept anything past 11/8, period.

Prof. Sullivan stops taking questions on the topic on 11/3

Using MyITLab and the “Assignments”


Charts and such

11

Excel Chapter 3


12

ObjectivesChoose a chart typeCreate a chart Modify a chartPrint charts


13

ChartsA chart is a graphic or visual representation

of dataMultiple chart types can enhance information,

adding visual appeal and making it easy to analyze data

14

Choosing a Chart Type

Graphic representation of dataAttractive, clear way to convey informationSelect the type of chart that best presents

your message Add enhancements to better communicate

your information

15

Choosing a Chart Type

Think about what you are trying to show/prove.

Tools for your future use (these are good!):Chart Suggestions (from Extreme Presentations)Periodic Table of Visualization Methods (from

Visual-Literacy.org)Chart Advisor (Microsoft Office Labs)

http://extremepresentation.typepad.com/files/choosing-a-good-chart-09.pdf

http://www.visual-literacy.org/periodic_table/periodic_table.html

http://www.officelabs.com/projects/chartadvisor/Pages/default.aspx


16

Parts of a Chart

Data point - numeric value that describes a single item on a chart

Data series - group of related data pointsCategory label - describes a group of data

points in a chartThe X or horizontal axis depicts

categorical labelsThe Y or vertical axis depicts numerical

valuesThe plot area contains graphical

representation of values in data seriesThe chart area contains entire chart and

all of its elements


17

Parts of a Chart


18

Parts of a Chart


19

Common Types of Charts


Column Charts

Column chart displays the revenue of software sales by cityThe height of the column reflects revenue of each cityPittsburgh has the highest revenue and Buffalo has the

lowest revenue

20


21

Add a 3-D Effect

3-D can enhance the display of one set of data


22

Clustered vs. StackedA multiple data series chart compares two

or more sets of dataClustered column chart

Groups similar data in columns Makes visual comparison easier

Stacked column chart Places (stacks) data in one column with each data

series in a different color for each category


23

Clustered Column Chart

Shows totals for each software category in a uniquely colored column


24

Stacked Column Chart

Total sales in the Y-axis would go up as the total sales go up


25

Bar Charts

Column charts with a horizontal orientation Emphasizes the difference between items


Bar Chart

Clustered bar chart shows totals for each software category in a uniquely colored bar

26


27

Pie Charts

Effective way to display proportional relationships

The pie denotes the total amountEach slice corresponds to its respective

percentage of the total


28

Pie Chart


29

Exploded Pie Charts

Exploded pie charts can be used to emphasize one or more slices of the pie


30

Line ChartShows trends over a long period of timeA line is used to connect data points


31

Line Charts


32

Doughnut Chart

Displays values as percentages of the whole

Shows values for each category in each market area

Unlike pie chart, displays multiple sets of data


33

Scatter Chart

Shows a relationship between two variables

Often used in statistical analysis and scientific studies


34

Stock Chart

Shows the high, low, and close prices for individual stocks over a period of time


35

Creating a ChartSix main steps to create a chart

Specify the data seriesSelect the range of cells to chartSelect the chart typeInsert the chart and designate the chart locationChoose chart options/add graphics in chartsChange the chart location and size


Print Charts

36

You can print a chart:Including the worksheet in which it is embeddedThat is embedded, without printing the worksheetThat was placed on a separate worksheet

Always Print Preview to ensure you are printing what you intended

Select Print from the File menu or click the Print button on the Standard Toolbar


37

last week lightening review copyright © 2008 pearson prentice hall. all rights reserved. 1 formulas...

Documents

pearson prentice hall

type of chart

chart typecreate

chartsa chart

lookup value

excel chapter

vlookup labcopyright

excel exam