- #1
hoodrych
- 17
- 0
Before you begin reading, I want to say thank you for helping, or attempting to help me. I really appreciate any help you can give me! Also, warning: Wall of Text approaching fast.
EDIT: I've been informed by Stephen Tashi on this forum that I am misusing the term Confidence Interval. What I am actually looking for appears to be the "Prediction Interval". The person who assigned me this task used the term CI but it looks like it is misused all the time, and that he meant PI.
Objectives:
Please note: I am doing this in excel, so I will be using excel functions to calculate stdev ect. But if you aren't familiar with excel you can still be of help to me! The concepts are what I need help with!
Here an explanation of the data I was given and what I have done so far:
System #1 and #2 BOTH have two lists of 48 numbers. One list being the closing market price in USD and the other list the closing market price in the native currency.
So I have "Sys#1 USD", "Sys#1 Native", "Sys#2 USD" and "Sys#2 Native" columns of 48 values each. System #1 is the actual closing mkt price, while System #2 is the one we are testing to see how much it differs from the correct values.
(1.)
I found the exchange rate to the dollar for each system by simply dividing Native/USD for the corresponding system.
(2.)
I then found the percent error of the foreign exchange rates of System 2 compared to System 1.
[PLAIN]http://www.pstcc.edu/departments/natural_behavioral_sciences/E2010D0101.gif
(3.)
I proceeded to find the standard deviation and mean using the simple functions excel comes equipped with. Excel functions below.
(4.)
I was informed that using the =CONFIDENCE function in excel was actually NOT what I want because it calculated the CI with the true mean of all future data, and I do not know the true mean value of all future data, only of my sample of 48 days.
I was told to use the =NORMINV(probability,mean,standard_dev) function by my coworker. To my understanding, this method "fits a normal distribution to the data and then makes a prediction assuming that this fit is correct."
I'm not sure if my data is a normal distribution, so do not know if I can use =NORMINV?
So basically, how do I calculate a 95% confidence interval of this data and determine the best fit distribution? Should I be using =NORMINV?
Thank you so much for your help!
EDIT: I've been informed by Stephen Tashi on this forum that I am misusing the term Confidence Interval. What I am actually looking for appears to be the "Prediction Interval". The person who assigned me this task used the term CI but it looks like it is misused all the time, and that he meant PI.
Objectives:
- Quantify the variability of exchange rates between system #1 and system #2
- Determine bestfit distribution
- Find 95% confidence interval
Please note: I am doing this in excel, so I will be using excel functions to calculate stdev ect. But if you aren't familiar with excel you can still be of help to me! The concepts are what I need help with!
Here an explanation of the data I was given and what I have done so far:
System #1 and #2 BOTH have two lists of 48 numbers. One list being the closing market price in USD and the other list the closing market price in the native currency.
So I have "Sys#1 USD", "Sys#1 Native", "Sys#2 USD" and "Sys#2 Native" columns of 48 values each. System #1 is the actual closing mkt price, while System #2 is the one we are testing to see how much it differs from the correct values.
(1.)
I found the exchange rate to the dollar for each system by simply dividing Native/USD for the corresponding system.
(#1Native/#1USD) = (#1Native/#1 $1 USD) and (#2Native/#2USD) = (#2Native/#2 $1 USD)
(2.)
I then found the percent error of the foreign exchange rates of System 2 compared to System 1.
[PLAIN]http://www.pstcc.edu/departments/natural_behavioral_sciences/E2010D0101.gif
[ ( (abs[(#2Native/#2 $1USD) - (#1Native/#1 $1USD)]) / (#1Native/#1 $1USD) ] * 100
(3.)
I proceeded to find the standard deviation and mean using the simple functions excel comes equipped with. Excel functions below.
=STDEV(values)
=AVERAGE(values)
(4.)
I was informed that using the =CONFIDENCE function in excel was actually NOT what I want because it calculated the CI with the true mean of all future data, and I do not know the true mean value of all future data, only of my sample of 48 days.
I was told to use the =NORMINV(probability,mean,standard_dev) function by my coworker. To my understanding, this method "fits a normal distribution to the data and then makes a prediction assuming that this fit is correct."
I'm not sure if my data is a normal distribution, so do not know if I can use =NORMINV?
So basically, how do I calculate a 95% confidence interval of this data and determine the best fit distribution? Should I be using =NORMINV?
Thank you so much for your help!
Last edited by a moderator: