Loading...

ADVANCED EXCEL

Open Posted By: surajrudrajnv33 Date: 23/02/2021 Graduate Rewriting & Paraphrasing

From the PPT Descriptive Statistics, slide 3: Take the data about the cellphone minutes and enter in an excel file and perform descriptive statistics data analysis.

Find these variable using excel formulas as done in class  (min, max, mode, mean, Quartile 1, Quartile 3, median, and standard deviation).

Show a Box and Whisker Graph for this dataset in a separate tab.

Category: Engineering & Sciences Subjects: Biology Deadline: 12 Hours Budget: $120 - $180 Pages: 2-3 Pages (Short Assignment)

Attachment 1

3-*

GOALS

Calculate the arithmetic mean, weighted mean, median, mode, and geometric mean.

Explain the characteristics, uses, advantages, and disadvantages of each measure of location.

Identify the position of the mean, median, and mode for both symmetric and skewed distributions.

Compute and interpret the range, mean deviation, variance, and standard deviation.

Understand the characteristics, uses, advantages, and disadvantages of each measure of dispersion.

Understand Chebyshev’s theorem and the Empirical Rule as they relate to a set of observations.

*

3-*

Parameter Versus Statistics

PARAMETER A measurable characteristic of a population.

STATISTIC A measurable characteristic of a sample.

*

3-*

Population Mean

For ungrouped data, the population mean is the sum of all the population values divided by the total number of population values. The sample mean is the sum of all the sample values divided by the total number of sample values.

EXAMPLE:

*

3-*

The Median

PROPERTIES OF THE MEDIAN

There is a unique median for each data set.

It is not affected by extremely large or small values and is therefore a valuable measure of central tendency when such values occur.

It can be computed for ratio-level, interval-level, and ordinal-level data.

It can be computed for an open-ended frequency distribution if the median does not lie in an open-ended class.

EXAMPLES:

The ages for a sample of five college students are:

21, 25, 19, 20, 22

Arranging the data in ascending order gives:

19, 20, 21, 22, 25.

Thus the median is 21.

The heights of four basketball players, in inches, are:

76, 73, 80, 75

Arranging the data in ascending order gives:

73, 75, 76, 80.

Thus the median is 75.5

MEDIAN The midpoint of the values after they have been ordered from the smallest to the largest, or the largest to the smallest.

*

3-*

The Mode

MODE The value of the observation that appears most frequently.

*

3-*

The Relative Positions of the Mean, Median and the Mode

*

3-*

The Geometric Mean

  • Useful in finding the average change of percentages, ratios, indexes, or growth rates over time.
  • It has a wide application in business and economics because we are often interested in finding the percentage changes in sales, salaries, or economic figures, such as the GDP, which compound or build on each other.
  • The geometric mean will always be less than or equal to the arithmetic mean.
  • The formula for the geometric mean is written:

EXAMPLE:

Suppose you receive a 5 percent increase in salary this year and a 15 percent increase next year. The average annual percent increase is 9.886, not 10.0. Why is this so? We begin by calculating the geometric mean.

*

3-*

Measures of Dispersion

  • A measure of location, such as the mean or the median, only describes the center of the data. It is valuable from that standpoint, but it does not tell us anything about the spread of the data.
  • For example, if your nature guide told you that the river ahead averaged 3 feet in depth, would you want to wade across on foot without additional information? Probably not. You would want to know something about the variation in the depth.
  • A second reason for studying the dispersion in a set of data is to compare the spread in two or more distributions.
  • RANGE
  • MEAN DEVIATION
  • VARIANCE AND STANDARD DEVIATION

*

3-*

EXAMPLE – Mean Deviation

EXAMPLE:

The number of cappuccinos sold at the Starbucks location in the Orange Country Airport between 4 and 7 p.m. for a sample of 5 days last year were 20, 40, 50, 60, and 80. Determine the mean deviation for the number of cappuccinos sold.

Step 1: Compute the mean

Step 2: Subtract the mean (50) from each of the observations, convert to positive if difference is negative

Step 3: Sum the absolute differences found in step 2 then divide by the number of observations

*

3-*

Variance and Standard Deviation

  • The variance and standard deviations are nonnegative and are zero only if all observations are the same.
  • For populations whose values are near the mean, the variance and standard deviation will be small.
  • For populations whose values are dispersed from the mean, the population variance and standard deviation will be large.
  • The variance overcomes the weakness of the range by using all the values in the population

VARIANCE The arithmetic mean of the squared deviations from the mean.

STANDARD DEVIATION The square root of the variance.

*

3-*

EXAMPLE – Population Variance and Population Standard Deviation

The number of traffic citations issued during the last five months in Beaufort County, South Carolina, is reported below:

What is the population variance?

Step 1: Find the mean.

Step 2: Find the difference between each observation and the mean, and square that difference.

Step 3: Sum all the squared differences found in step 3

Step 4: Divide the sum of the squared differences by the number of items in the population.

*

3-*

Sample Variance and
Standard Deviation

EXAMPLE

The hourly wages for a sample of part-time employees at Home Depot are: $12, $20, $16, $18, and $19.

What is the sample variance?

*

3-*

Chebyshev’s Theorem and Empirical Rule

The arithmetic mean biweekly amount contributed by the Dupree Paint employees to the company’s profit-sharing plan is $51.54, and the standard deviation is $7.51. At least what percent of the contributions lie within plus 3.5 standard deviations and minus 3.5 standard deviations of the mean?

*

3-*

EXAMPLE:

Determine the arithmetic mean vehicle selling price given in the frequency table below.

The Arithmetic Mean and Standard Deviation of Grouped Data

EXAMPLE

Compute the standard deviation of the vehicle selling prices in the frequency table below.

*

09886

1

15

1

05

1

.

)

.

)(

.

(

GM

=

=

50

5

80

60

50

40

20

=

+

+

+

+

=

=

å

n

x

x

124

12

488

,

1

)

(

2

2

=

=

-

=

å

N

X

m

s

29

12

348

12

10

34

...

17

19

=

=

+

+

+

+

=

=

å

N

x

m

sample

the

in

ns

observatio

of

number

the

is

sample

the

of

mean

the

is

sample

the

in

n

observatio

each

of

value

the

is

variance

sample

the

is

:

Where

2

n

X

X

s