gr8wizard10
Badges: 21
Rep:
?
#1
Report Thread starter 4 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
SarcasticMel
Badges: 2
Rep:
?
#2
Report 4 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
gr8wizard10
Badges: 21
Rep:
?
#3
Report Thread starter 4 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
samba
Badges: 3
Rep:
?
#4
Report 4 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
gr8wizard10
Badges: 21
Rep:
?
#5
Report Thread starter 4 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
samba
Badges: 3
Rep:
?
#6
Report 4 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
gr8wizard10
Badges: 21
Rep:
?
#7
Report Thread starter 4 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
samba
Badges: 3
Rep:
?
#8
Report 4 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

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

See more of what you like on
The Student Room

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

Personalise

How has the start of this academic year been for you?

Loving it - gonna be a great year (127)
18.12%
It's just nice to be back! (192)
27.39%
Not great so far... (251)
35.81%
I want to drop out! (131)
18.69%

Watched Threads

View All