4-3.2. Confidence Interval for Population Proportion in Excel

Loading...

來自 University of Illinois at Urbana-Champaign 的課程

Exploring and Producing Data for Business Decision Making

211 個評分

University of Illinois at Urbana-Champaign

Exploring and Producing Data for Business Decision Making

211 個評分

課程 5（共 7 門，Specialization Managerial Economics and Business Analysis ）

This course provides an analytical framework to help you evaluate key problems in a structured fashion and will equip you with tools to better manage the uncertainties that pervade and complicate business processes. Specifically, you will be introduced to statistics and how to summarize data and learn concepts of frequency, normal distribution, statistical studies, sampling, and confidence intervals. While you will be introduced to some of the science of what is being taught, the focus will be on applying the methodologies. This will be accomplished through the use of Excel and data sets from many different disciplines, allowing you to see the use of statistics in very diverse settings. The course will focus not only on explaining these concepts, but also understanding the meaning of the results obtained. Upon successful completion of this course, you will be able to: • Summarize large data sets in graphical, tabular, and numerical forms. • Understand the significance of proper sampling and why you can rely on sample information. • Understand why normal distribution can be used in so many settings. • Use sample information to infer about the population with a certain level of confidence about the accuracy of the estimations. • Use Excel for statistical analysis. This course is part of the iMBA offered by the University of Illinois, a flexible, fully-accredited online MBA at an incredibly competitive price. For more information, please see the Resource page in this course and onlinemba.illinois.edu.

從本節課中

Module 4: Inference

You have sample data and have done the analysis – you think you can say something about the population based on your sample study. But, do you have a sense of what are the chances of you being right or wrong? How can you be surer? What else should you have considered? In this module, you will learn how to find the answers to these questions.

4-3.1. Confidence Interval for Population Proportion10:40

4-3.2. Confidence Interval for Population Proportion in Excel8:45

4-3.3. Confidence Interval Animation in Excel5:20

4-3.4. Starting Salary Example in Excel7:51

與講師見面

Fataneh Taghaboni-Dutta
Clinical Professor of Business Administration
University of Illinois, Urbana-Champaign College of Business Department of Business Administration

[SOUND]

In this video, I'm going to show you how to calculate confidence interval for

population proportion.

The data I'm using, is the data we downloaded from New York Stock Exchange

closing data, at the end of a day, in September of 2015.

Symbols have been changed to comply with copyright laws.

Let's say, that in this situation, I'm interested in knowing what proportion of

the stocks had a positive change at the end of the day.

So, what we have recorded from the New York Stock Exchange,

the value of the stock at the end of the day.

What was the net change that it had and whether it was a negative change or

a positive change.

So, it has gone up, if this percentage is positive and it's gone down,

if this value is negative.

If I'm interested in knowing the proportion of what has gone up or not,

first I have to classify each stock as a positive value or a negative value.

So, how am I going to do that?

I'm going to answer a question like, has it gone up?

And to do this, I'm going to use a logical function from Excel.

So I'm going to say, if this value that I see here is a positive value,

so it's greater than 0, then return a value of 1.

Otherwise, return a value of 0.

So if you return this, and if I've done it correctly,

the first start that I have classifies as a 0, because it did not go up.

So, 0 means false.

No, it didn't go up.

And let me just copy this down until we get to a positive value.

Here we have a stock that went up on that day.

So if I've done this correctly, this should return a value of 1,

and indeed it does.

For everything else in between,

because everything is negative, it returned a value of 0, here.

Because the value that sits in the cell that I'm checking, the 11 is

greater than 0, it returns a 1 and I get to know that's a stock that has gone up.

So, you can essentially copy these down all the way.

So, I put my cursor here, double click, and it populates the entire thing.

So again, we have all of the stocks at that a,

which is over 2100 stocks recorded here.

So first, I'm going to find out how many went up.

I'm going to say number that went up.

And that number is simply sum of everything here.

Remember they get a 0 if they didn't go up.

So if I add all the 1's,

I will find out what was total number that went up at the end of that day.

So, Ctrl+Shift+down.

Close the parentheses, return, scroll up to see the number.

So, 383 stocks on that day went up.

So, if I want to know what is the proportion, I have to take this number and

divided by the total number of stocks that were reported back at the end of the day.

So, in order for me to know how many starts are in this column, or

basically in this column, I need to find the total number, which is count.

Count is simply, I can ask Excel to count the number of values that it finds here.

So, if I put my cursor again here, Ctrl+Shift+down,

close the parenthesis, return, I will see that on that day I had

2192 stocks that were reported in this data set.

So, what was the proportion of ups?

It is simply, 383 divided by 2192.

So, those two cells divided.

So, roughly about 17 and a half percent of the stocks went up, the rest did not.

So, this is definitely going to be a rough day for the Stock Market.

So, now let's look at what happens if we do sample.

So, an idea behind sampling is that you have the sample.

So, let's say You have 127 stocks that you have in your 401(k) or

you have invested in or whatever.

So, you basically hold a sample of the entire stock market.

How did you fare and can you estimate, based on your data,

what was the number of ups and downs in the stock market as a whole?

So, we will repeat the same thing here, I want to know how many went up

here in my stock, in my portfolio, and I'm going to repeat it the same way.

If this value is a positive value,

it is greater than zero, return a value of 1, otherwise return a value of 0, and

I'm going to say Return, put my cursor here and repeat it.

So, the entire 127 stocks that I own also gets repeated as 0 and 1.

So, what is the proportion for my portfolio?

So again, I'm going to pine the sum of the ups and

that will be the sum of the values I have here.

Then I'm going to look at the count, I should have 127 because

that's what I have told you but it never hurts to redo it, count,

and as I mentioned, I have 127, so now my proportion of up,

for my portfolio, is simply 20 divided by 127.

So, in terms of ups and downs,

there are only 15.7 5% that went up in mine.

So, can I come up with a confidence interval here?

So, if I assume a 95% confidence interval,

then what I know is that my z value is going to be, I know it's 1.96 but

I'm just going to show it to you, remember I have .975 here and I get the 1.96.

This is my z value.

If you need a reminder of why I had to put that, is because if you look at the normal

distribution, I'm saying that I'm going to create the 95th percentile.

So 2 1/2 % is here.

2 1/2% is here.

If you look at this z value.

Everything to the left of it, is that .95 that's in the middle and this .025.

So, you're same point .975 would have a z of 1.96.

I'm trying to come up with my confidence interval but

remember what is the confidence interval.

Confidence interval is your p hat.

Plus or minus z of alpha over 2 times the standard error,

which is your p minus 1 p divided by n.

This is my standard error, so I'm going to calculate my standard error here.

And my standard error is, square root of.

And I'm going to use as many parenthesis as I need to make sure that my order of

operations is correct.

So it's p hat, times 1 minus the p hat and

this is going to be the numerator under that

square root, divided by my sample size and

in this case is my count, 127.

Now, I'm ready to close my parentheses and return.

So, now I can calculate my margin of error.

And my margin of error is simply your z value times your standard error.

So now I can highlight the values that I'm going to use.

One would be margin of error and the other one would be my sample proportion.

So, focus on what I have highlighted.

So now my 95% confidence interval would be the lower part of it.

And the upper part of it is going to be the sample proportion

which is right here minus the margin of error.

And upper would be sample proportion plus the margin of error.

If I'm going to use my portfolio as a sample, then based on what I have

experienced in my portfolio, I suspect that about

9.4% to 22% is what the Stock Market

has experienced in terms of proportion of stocks that went up on that day.

And of course, in my case, my sample would have given me the right answer.

Why?

Because looking back on that day, I see the true proportion was 17.5% and

clearly this interval will capture 17.5%.

This is how we can use sample proportion to create a confidence interval,

an estimate for what the population proportion might have been.

探索我們的目錄

免費加入並獲得個性化推薦、更新和優惠。

Coursera 致力於普及全世界最好的教育，它與全球一流大學和機構合作提供在線課程。

© 2018 Coursera Inc. 保留所有權利。

通過 App Store 下載

通過 Google Play 獲取