# Confidence Interval [SpreadSheet Probem]

Watch
Announcements
Thread starter 6 years ago
#1
Trying to find the right formulation to work out the confidence interval. Trusty future bankers could possibly help a guy out. Excel kings 2k15s

The answer refers to the data set on the left and my solution for a 99% confidence interval for profits given the data is [£4,106.92, £19,351.08]

My question is do I input alpha as 0.005 as oppose to 0.01 on an excel spreadsheet to find a 99% confidence interval.

Formula for confidence range is in the input formula box above, to highlight what cells were used.
0
reply
6 years ago
#2
Bit small to see, but the rule is:

1 -tail: alpha on that side
2 -tails: alpha/2 on each side.

So really all you have to figure out is if this is 1- or two-tailed.

ps why? Because 99% C.I. means 99 times out of 100 your draw will be in that interval. sometimes you don't care if it's below or above so you can have 0.5% below and 0.5% above. But other times you may want the C.I. so that the one time it is not in it is say above it.
0
reply
Thread starter 6 years ago
#3
(Original post by SarcasticMel)
Bit small to see, but the rule is:

1 -tail: alpha on that side
2 -tails: alpha/2 on each side.

So really all you have to figure out is if this is 1- or two-tailed.

ps why? Because 99% C.I. means 99 times out of 100 your draw will be in that interval. sometimes you don't care if it's below or above so you can have 0.5% below and 0.5% above. But other times you may want the C.I. so that the one time it is not in it is say above it.
Thanks, I understand the mathematical logic, I just want to be able to summarise large data via the use of excel.

I found Excel's illustration of how the =CONFIDENCE.NORM() function operates.
Example of usage

Assume that intelligence quotient (IQ) scores follow a normal distribution with standard deviation 15. You test IQs for a sample of 50 students in your local school and obtain a sample mean of 105. You want to compute a 95% confidence interval for the population mean. A 95% or 0.95 confidence interval corresponds to alpha = 1 – 0.95 = 0.05.

So I assume I can input an alpha of 0.01 for a 99% CI, based on the above?
0
reply
6 years ago
#4
(Original post by Abdul-Karim)
Thanks, I understand the mathematical logic, I just want to be able to summarise large data via the use of excel.

I found Excel's illustration of how the =CONFIDENCE.NORM() function operates.
Example of usage

Assume that intelligence quotient (IQ) scores follow a normal distribution with standard deviation 15. You test IQs for a sample of 50 students in your local school and obtain a sample mean of 105. You want to compute a 95% confidence interval for the population mean. A 95% or 0.95 confidence interval corresponds to alpha = 1 – 0.95 = 0.05.

So I assume I can input an alpha of 0.01 for a 99% CI, based on the above?
You're going around in circles. 1-alpha is the level of confidence (or alpha+confidence=1). It's set to 0.01 atm for 99%. More data = smaller radius of confidence. If the excel sheet you stole allows you to alter alpha, you can do it. If it's hardcoded, you can't? But it's set to 99% anyway so...
0
reply
Thread starter 6 years ago
#5
(Original post by samba)
You're going around in circles. 1-alpha is the level of confidence (or alpha+confidence=1). It's set to 0.01 atm for 99%. More data = smaller radius of confidence. If the excel sheet you stole allows you to alter alpha, you can do it. If it's hardcoded, you can't?
Excel sheet I stole? Designing models as part of my coursework.

Yes I understand, so an alpha of 0.01 would represent a 99% CI in excel when inputting alpha into formula

=CONFIDENCE.NORM(Alpha, Standard Deviation, Size)

I can change alpha variable. This formula essentially calculates [(X)+/- [(Z score confidence interval)]*(Variance/Number of Observations)^1/2]

Variance/Number of Observations = Standard Error

I was just confused as when I write it on paper, I cut 0.5% from each end on a normal distribution and was wondering whether it would translate over to excel.
0
reply
6 years ago
#6
(Original post by Abdul-Karim)
Yes I understand, so an alpha of 0.01 would represent a 99% CI in excel when inputting alpha into formula

=CONFIDENCE.NORM(Alpha, Standard Deviation, Size)

I can change alpha variable. This formula essentially calculates [(X)+/- [(Z score confidence interval)]*(Variance/Number of Observations)^1/2]

Variance/Number of Observations = Standard Error

I was just confused as when I write it on paper, I cut 0.5% from each end on a normal distribution and was wondering whether it would translate over to excel.
I'm not understanding what the problem/question is then?

You know what you're doing and how to do it... Your initial question was "My question is do I input alpha as 0.005 as oppose to 0.01 on an excel spreadsheet to find a 99% confidence interval."

Which obviously you could do if you pissed around with it, but why would you want to intentionally alter it to an incorrect alpha/formula to find the same results as you'd get by just using the 0.01 in the first place.
0
reply
Thread starter 6 years ago
#7
(Original post by samba)
I'm not understanding what the problem/question is then?

You know what you're doing and how to do it... Your initial question was "My question is do I input alpha as 0.005 as oppose to 0.01 on an excel spreadsheet to find a 99% confidence interval."

Which obviously you could do if you pissed around with it, but why would you want to intentionally alter it to an incorrect alpha/formula to find the same results as you'd get by just using the 0.01 in the first place.
Because a coursemate told me I had to divide alpha by 2 so I was confused and needed clarification.

I didn't want to calculate the whole thing on paper to ensure I was using the right number in placement of alpha within the function.

But that's cleared this up
0
reply
6 years ago
#8
(Original post by Abdul-Karim)
Because a coursemate told me I had to divide alpha by 2 so I was confused and needed clarification.

I didn't want to calculate the whole thing on paper to ensure I was using the right number in placement of alpha within the function.

But that's cleared this up
He might think it's a 2 tailed test? But as you said you understand the maths I assume that's not the case.

In a 2 tailed test half of the alpha is assigned to each tail (area under the curve)
0
reply
X

### Quick Reply

Write a reply...
Reply
new posts Back
to top
Latest
My Feed

### Oops, nobody has postedin the last few hours.

Why not re-start the conversation?

see more

### See more of what you like onThe Student Room

You can personalise what you see on TSR. Tell us a little about yourself to get started.

### Poll

Join the discussion

#### Do you think receiving Teacher Assessed Grades will impact your future?

I'm worried it will negatively impact me getting into university/college (58)
38.16%
I'm worried that I’m not academically prepared for the next stage in my educational journey (16)
10.53%
I'm worried it will impact my future career (7)
4.61%
I'm worried that my grades will be seen as ‘lesser’ because I didn’t take exams (33)
21.71%
I don’t think that receiving these grades will impact my future (24)
15.79%
I think that receiving these grades will affect me in another way (let us know in the discussion!) (14)
9.21%

View All
Latest
My Feed

### Oops, nobody has postedin the last few hours.

Why not re-start the conversation?

### See more of what you like onThe Student Room

You can personalise what you see on TSR. Tell us a little about yourself to get started.