using excel

8/25/2015

1

Using Excel

• Program used to organize data• Produce tables• Perform calculations• Make graphs• Perform statistical tests

Organizing data in tables

• Allows you to arrange data in a format thatis best for analysis

• The following are the steps you would use:

8/25/2015

2

8/25/2015

3

Performing calculations

• Allows you to perform several calculations• Sum, Average, Variance, Standard

deviation• Basic subtraction, addition, multiplication• More complex formulas

8/25/2015

4

8/25/2015

5

8/25/2015

6

8/25/2015

7

8/25/2015

8

8/25/2015

9

8/25/2015

10

8/25/2015

11

• Bar Charts…….

• Scatter Plots………………….

0

0.10.2

0.3

0.40.5

0.6

0.70.8

0.9

1 2 3

-1

0

1

2

3

4

5

6

7

8

9

0 0.2 0.4 0.6 0.8 1 1.2

Making graphs

8/25/2015

12

8/25/2015

13

8/25/2015

14

8/25/2015

15

8/25/2015

16

8/25/2015

17

8/25/2015

18

• Bar Charts…….

• Scatter Plots………………….

0

0.10.2

0.3

0.40.5

0.6

0.70.8

0.9

1 2 3

-1

0

1

2

3

4

5

6

7

8

9

0 0.2 0.4 0.6 0.8 1 1.2

Making graphs

8/25/2015

19

8/25/2015

20

8/25/2015

21

8/25/2015

22

8/25/2015

23

Analyzing Data in Excel

Statistical tests can be done to determine:• Whether or not there is a significant difference

between two data sets (Student’s t-test)• Whether or not there is a significant difference

between more than two data sets (ANOVA)• Whether or not there is a significant relationship

between two variables (Regression analysis)

Analyzing Data in Excel

The following steps must be followed:1. Choose an appropriate statistical test2. State H0 and HA

3. Run test to produce Test Statistic4. Examine P-value5. Decide to accept or reject H0

8/25/2015

24

• Normally, you would have to calculate the criticalvalue and look up the P value on a table

• All tests done in Excel provide the P value for you• This P value is used to determine the significance of

statistical results• This P value must be compared to an α value• α value is usually 0.05 or less (e.g. 0.01)• Less than 5% chance that the null hypothesis is true• The lower the α value the more certain we about

rejecting the null Hypothesis• First thing you must do is select which statistical test

you want to perform• This is how it is done……..

Analyzing Data in Excel

8/25/2015

25

8/25/2015

26

t-Tests

• Used to compare the means of two populations and answer thequestion:Is there a significant difference between the two populations?

• Example: Is there a significant difference between the averageheight of pine trees from 2 sites in Everglades National Park?

• You cannot use this test to compare two different types of data(e.g. water depth data and soil depth data).

• It can only compare two sets of data based on the same datatype (e.g. water depth data from two different sites)

• The two data sets that are being compared must be presentedin the same units. (e.g. you can compare two sets of data ifboth are recorded in days. You cannot compare data recordedin units of days with data recorded in units of months)

8/25/2015

27

• Your Null Hypothesis is always:

There is no significant difference between the twocompared populations (μ1= μ2)

• Your Alternative Hypothesis is always:

There is a difference between the two comparedpopulations (μ1 ≠ μ2)

1. Choose an appropriate statistical test2. State H0 and HA3. Run test to produce Test Statistic4. Examine P-value5. Decide to accept or reject H0

1. Choose an appropriate statistical test2. State H0 and HA3. Run test to produce Test Statistic4. Examine P-value5. Decide to accept or reject H0

8/25/2015

28

8/25/2015

29

8/25/2015

30

t-Tests1. Choose an appropriate statistical test2. State H0 and HA3. Run test to produce Test Statistic4. Examine P-value5. Decide to accept or reject H0

• When you run the test, look for the p-value

• If p > 0.05 then fail to reject your Null Hypothesis and state that“there is no significant difference between the two comparedpopulations”

• If p < 0.05 then reject your Null Hypothesis and state that “thereis a significant difference between the two comparedpopulations”

8/25/2015

31

t-Tests1. Choose an appropriate statistical test2. State H0 and HA3. Run test to produce Test Statistic4. Examine P-value5. Decide to accept or reject H0

• When you run the test, look for the p-value

• Our results show P = 0.09903

• Therefore P > 0.05 (This means that there is greater than a 5%chance that our null hypothesis is true)

• So we must fail to reject the Null Hypothesis and state that “thereis no significant difference between the two comparedpopulations”

ANOVA

• Used to compare the means of more than two populations andanswer the question:Is there a significant difference between the populations?

• Example: Is there a significant difference between the averageheight of pine trees from 4 sites in Everglades National Park?

• For comparing a particular feature of two or more populations,use a Single Factor ANOVA

• For comparing a particular feature of two or more populations,subdivided into two groups, use a Two Factor ANOVA

0102030405060708090

100

Number of Students

Num

ber o

f Dai

ly B

eers

MicroEcoBuisinessStatistics

8/25/2015

32

• Your Null Hypothesis is always:

There is no significant difference between thecompared populations (μ1 = μ2 = μ3 = μ4 …..)

• Your Alternative Hypothesis is always:

There is a difference between the comparedpopulations (μ1 ≠ μ2 ≠ μ3 ≠ μ4 …..)

1. Choose an appropriate statistical test2. State H0 and HA3. Run test to produce Test Statistic4. Examine P-value5. Decide to accept or reject H0

1. Choose an appropriate statistical test2. State H0 and HA3. Run test to produce Test Statistic4. Examine P-value5. Decide to accept or reject H0

8/25/2015

33

8/25/2015

34

ANOVA1. Choose an appropriate statistical test2. State H0 and HA3. Run test to produce Test Statistic4. Examine P-value5. Decide to accept or reject H0

• When you run the test, look for the p-value

• If p > 0.05 then fail to reject your Null Hypothesis and state that“there is no significant difference between the comparedpopulations”

• If p < 0.05 then reject your Null Hypothesis and state that “thereis a significant difference between at least two of the comparedpopulations”

0102030405060708090

100

Number of Students

Num

ber o

f Dai

ly B

eers

MicroEcoBuisinessStatistics

8/25/2015

35

ANOVA1. Choose an appropriate statistical test2. State H0 and HA3. Run test to produce Test Statistic4. Examine P-value5. Decide to accept or reject H0

• When you run the test, look for the p-value

• Our results show P = 0.002197

• Therefore P < 0.05 (This means that there is less than a 5%chance that our null hypothesis is true)

• So we must reject your Null Hypothesis and state that “there is asignificant difference between at least two of the comparedpopulations”

0102030405060708090

100

Number of Students

Num

ber o

f Dai

ly B

eers

MicroEcoBuisinessStatistics

ANOVA• Remember:The ANOVA result will only tell you thati) None of the data sets are significantly

different from each otherOR

ii) At least two of the data sets among the datasets being compared are significantlydifferent

• If there is a significant difference between atleast two data sets, it will not tell you whichtwo.

8/25/2015

36

Regression analysis

• Used to determine whether or not there is a linear relationshipbetween two variables and answer the question:Is there a significant linear relationship between two variables?

• Example: Is there a significant relationship between theaverage height of pine trees and soil depth in EvergladesNational Park?

• It basically creates an equation (or line) that best predicts Yvalues based on X values.

• You cannot use this test to compare populations. It onlycompares variables.

• You are looking at two different variables (e.g. water depth (cm)and plant abundance (no. of individuals), so the data sets donot have to be presented in the same units

0.00

10.00

20.00

30.00

40.00

50.00

60.00

0 1 2 3 4 5 6

Price of Whiskey ($)

Mon

ey S

pent

by

TA ($

)

• Your Null Hypothesis is always:

There is no significant linear relationship between thetwo variables

• Your Alternative Hypothesis is always:

There is a significant linear relationship between thetwo variables

1. Choose an appropriate statistical test2. State H0 and HA3. Run test to produce Test Statistic4. Examine P-value5. Decide to accept or reject H0

8/25/2015

37

Example: R square value of 0.04• The regression line does not fit the

data well• Many of the points lie far from the

line, so there is not a defined linearrelationship between the twovariables

• “x” cannot be used to predict “y”

Example: R square value of 0.94• The regression line fits the data well• The points all lie fairly close to the

line, so there is a defined linearrelationship between the twovariables

• “x” can be used to predict “y”

0

0.2

0.4

0.6

0.8

1

1.2

0 0.2 0.4 0.6 0.8 1 1.2

Price of Whiskey ($)

Mon

ey S

pent

by

TA ($

)

0.00

10.00

20.00

30.00

40.00

50.00

60.00

0 1 2 3 4 5 6

Price of Whiskey ($)

Mon

ey S

pent

by

TA ($

)

• R squared: how well “y” can be predicted by “x”, i.e. howstrong the linear relationship is between the two variables.

• The closer R square is to 0, the less well it fits the data.• The closer R square is to 1, more it fits the data.

1. Choose an appropriate statistical test2. State H0 and HA3. Run test to produce Test Statistic4. Examine P-value5. Decide to accept or reject H0

8/25/2015

38

8/25/2015

39

8/25/2015

40

8/25/2015

41

Regression analysis1. Choose an appropriate statistical test2. State H0 and HA3. Run test to produce Test Statistic4. Examine P-value5. Decide to accept or reject H0

• When you run the test, look for the Significance F or Samplep-value

• If p > 0.05 then fail to reject your Null Hypothesis and statethat “There is no significant linear relationship between thetwo variables”

• If p < 0.05 then reject your Null Hypothesis and state that“There is a significant linear relationship between the twovariables”

0.00

10.00

20.00

30.00

40.00

50.00

60.00

0 1 2 3 4 5 6

Price of Whiskey ($)

Mon

ey S

pent

by

TA ($

)

1. Choose an appropriate statistical test2. State H0 and HA3. Run test to produce Test Statistic4. Examine P-value5. Decide to accept or reject H0

• When you run the test, look for the p-value

• Our results show Significance F or Sample p-value = 1.65E08 =0.0000000165

• Therefore P < 0.05 (This means that there is less than a 5%chance that our null hypothesis is true)

• So we must reject your Null Hypothesis and state that “There isa significant linear relationship between the two variables”

• Next look at the R squared value

• Our results show R squared = 0.975

• Therefore the line fits the data well

• “x” can be used to predict “y”

Regression analysis

0.00

10.00

20.00

30.00

40.00

50.00

60.00

0 1 2 3 4 5 6

Price of Whiskey ($)

Mon

ey S

pent

by

TA ($

)

8/25/2015

42

Ecological study

• What is the aim of the study?• What is the main question being asked?• What are your hypotheses?• Collect data• Summarize data in tables• Present data graphically• Statistically test your hypotheses• Analyze the statistical results• Present a conclusion to the proposed question

Aim: To determine whether or not there are changes in heights of Pine trees withdistance from the edge of a forest trail in Everglades National Park.Hypotheses:HO: There is no significant relationship between distance from the edge of the trail andPine tree heightHA: There is a significant relationship between distance from the edge of the trail andPine tree heightResults:

Discussion/Conclusion:The gap created by the trail may be adversely affecting Pine trees, such that they areshorter near the trail and become taller with distance from the trail.

Distance from trail (m) Plant heights (m)

0 2.1

5 2.7

10 2.9

15 3.1

20 3.4

25 3.7

30 3.8

35 4.5

40 4.6

45 4.8

50 5.6

SUM 41.2

AVERAGE 3.74

STANDARD DEVIATION 1.04

Change in tree height with distance from forest trail

01

23

456

0 10 20 30 40 50 60

Distance from trail

Tree

hei

ght (

m)

• P = 1.65E-08 Since P < 0.05, reject Ho• Therefore, there is a significant relationship

between distance from the edge of the trailand Pine tree height

• R Square = 0.97, so there is a strongpositive linear relationship betweendistance from the trail and plant height

Average tree height of pine trees along transectfrom forest trail to interior forest at ENP

8/25/2015

43

Three questions:1. T-test2. Single factor ANOVA3. Regression analysis

Assignment – Worksheet 1