Common Errors and Solutions in Excel Spreadsheet Formulas and Charting Data, Slides of Fundamentals of E-Commerce

Download Common Errors and Solutions in Excel Spreadsheet Formulas and Charting Data and more Slides Fundamentals of E-Commerce in PDF only on Docsity!

BASICAPPLLICATIONSOFTWARE

Common Errors Resulting in Spreadsheet Formulas

Lecture Objectives Charts Overview Charting Example

: Student Performance Charts

Common

Spreadsheet Charts Column/ Bar Chart Pie Chart Line Chart

Charts Overview A chart is a

graphical representation of data values

in a

worksheet. Charts^ allows easier interpretation of facts

(comparisons,

trends & patterns) hidden inside numbers. Every^ chart delivers a specific message

about the plotted

data. Nature of the chart

message implies the type of the chart

to

be used.

….Example: Charting Spreadsheet Data^ M IS 105 E xam S co re C om p ariso n by

S tu den t N am es

120.0 100.0 80.0 60.0 40.0 20.0^ 0.0A dam s^

B aker^ G las sm an

M oldof^

W alker S tude nt N am es Exam Scores

M ajor 1^ M ajor 2

Final

What is the rank of each student with respect to scores indifferent exams?

(Comparing Values)

ColumnChart

Columns are side- by-side orclustered (for each student).

….Example: Charting Spreadsheet Data^ Adam's Exam Score Percentages in his Total

Score

37% 33% 30%

Major 1 Major 2 Final

For Mr. Adam, what percentage of his total score comes fromeach exam?

(Comparing percentages in a total value)

Pie Chart

Chart Terminology

Data Point^ Plotted data values. Data Series

Name

A name given to a collection of data values which belong to asingle row or column (shown by colors). Category Labels A label for further defining (another dimension) a data valuein a series (shown by labels on X-axis).

Chart Terminology

MIS105 Exam Score Comparison by^ Student Names 120.0 100.0 80.0 60.0 40.0 20.0 0.0Adams^ Baker^

Glassman^ Moldof^

Walker Student Names Exam Scores

Major 1^ Major 2 Final

MIS105 Student Grade Sheet Name Major 1^ Major 2^ Final

Student Average Adams^ 100.

90.0^ 81.^

Baker^ 90.

76.0^ 87.^

Glassman^ 90.

78.0^ 78.^

Moldof^ 60.

60.0^ 40.^

Walker^ 80.

80.0^ 90.^

Class Average^ 84.

76.8^ 75. ●^ Every colored column refers to a data point. ● Every color identifies a series (collection of datapoints belonging to a specific exam) ● Every cluster (X-axis Label) identifies a category^ - Series

&^ Category Labels

could be used interchangeably. -It affects only the

organization of visual elements

(colored

Here, Series (Exam Name)– columns).

Appears in Columns Category (Student Name)–

appears in Rows.

Main Chart Types in Microsoft Excel Column Chart Bar Chart Pie Chart Line Chart Doughnut Chart Area/ Radar/ Surface/ Bubble/ Stock/ Cylinder/User Defined

Pie Chart Details

Pie Chart^ Used to

display proportionalrelationships^ among differentitems. Cannot plot more than oneseries. Interpretation: Circle (pie) shows the totalamount. Every slice shows thecontribution (%age) of eachdata value in the total. Key Words: percentage market share

Product Revenues by Products

47% 11%^ 42% Superior Software SalesWord Processing^ Spreadsheets^ Database Product Name^

1998 1999 2000 2001 Product Total Word Processing^ $50,000.00 $67,500.

$9,500.00 $141,000.

$268,000. $44,000.00 $18,000.00 $11,500.00 $105,000.00 Spreadsheets

$178,500. Database^ $12,000.

$7,500.00^ $6,000. $30,000.00^ $55,500. Yearly Total^ $107,998.00 $94,999.00 $29,000.00 $278,001.

$502,000.

What percentage of total revenue in1998 comes from each product?

Column Chart Details

Column/ Bar Chart^ Used^ to show/ compare actual numbers^ rather than percentages.^ Bar chart is only a horizontalrepresentation

of a column chart.

Interpretation

: The length of vertical column/ horizontalbar tells the actual value.

Key words:^ What is the rank of something among…?^ What is the value of each?^ Compare the values…?

$160,000.00$140,000.00$120,000.00$100,000.00$80,000.00$60,000.00$40,000.00$20,000.00$0.00^1998

1999 2000

Word Processing Spreadsheets Database 2001 Superior Software Sales Product Name^

1998 1999 2000

2001 Product Total Word Processing^ $50,000.00 $67,500.

$9,500.00 $141,000.^

$268,000. $44,000.00 $18,000.00 $11,500.00 $105,000.00 Spreadsheets

$178,500. Database^ $12,000.

$7,500.00^ $6,000.^ $30,000.00^ $55,500. Yearly Total^ $107,998.00 $94,999.00 $29,000.00 $278,001.

$502,000.

What is the revenue produced by eachproduct in each year?

… Column Chart Details

$160,000.00$140,000.00$120,000.00$100,000.00$80,000.00$60,000.00$40,000.00$20,000.00^ $0.00^1998

1999 2000

Word Processing Spreadsheets Database 2001 $0.00^ $20,000.00^ $40,000.00^ $60,000.

$80,000.00^ $100,000.00 $120,000.00 $140,000.00 $160,000. 2001 2000 1999 1998

Database Spreadsheets Word Processing

Column Chart

Bar Chart