2
$\begingroup$

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.

I've been informed that I am misusing the term Confidence Interval. I have been told that what I am actually looking for is the "Prediction Interval". I'm not sure though as I know my coworker who assigned me this task said Confidence Interval.

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.

[ ( (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!

$\endgroup$
1
$\begingroup$

You should read Spreadsheet Adiction and the links from that page before trusting any results from Excel.

From your question it appears that you don't have a firm grasp on what confidence intervals and prediction intervals are. You should really consult a good intro stats book, and/or take a class or meet with a consultant to get these concepts down. But here is a short explanation:

The condifence interval is a statement about where we believe the true population parameter (the mean above) to be based on the sample data. So not knowing the population mean does not mean that you cannot do a confidence interval. If your sample is large and you are willing to assume that the population is not overly skewed or would produce outliers, then the Central Limit Theorem says that a confidence interval on the mean based on the assumption of a normal population will be a good approximation even if the population is not normal. So you can use normal based theory without knowing if the population is normal as long as you are willing to make the above assumptions.

The prediction interval is a statement about where we expect future individual data points to be. This prediction will depend much more on the shape of the distribution.

The big difference in concept is whether you are talking about the mean of all future data, or individual data points (I could not tell which you are interested in from the question).

The norminv function in Excel does not fit a normal distribution, but gives the x-value for a given area under the curve (probability) for a normal with the specified mean and standard deviation. That function could be used as part of the computations to get either of the intervals, but that assumes that you know the population standard deviation, if you are using the sample standard deviation then it is more appropriate to use the t distribution rather than the normal. Also note that the prediction interval takes into account the uncertainty in you estimate of the mean and standard deviation in addition to the randomness of the individual data points, so norminv probably is not what you want.

$\endgroup$
  • $\begingroup$ @Greg Snow @GregSnow Thanks for your help. Few questions. You say NORMINV does not fit normal distribution yet you say "for a given area under the curve (probability) for a normal with the specified...". What do you mean by "for a normal" then? Second, I do not know anything about the population, and it's impossible for me to. The population is the values that System 1 and 2 spit out every day at market close. So you are saying I should use a t distribution since all I have to go off of is the 48 values from my sample? Once again, I cannot thank you enough for your help! $\endgroup$ – chhhhhh Jun 17 '11 at 13:54
  • $\begingroup$ One last question: "Also note that the prediction interval takes into account the uncertainty in you estimate of the mean and standard deviation in addition to the randomness of the individual data points". How/why/in what way does it take into account the uncertainty of my stdev and mean estimate? $\endgroup$ – chhhhhh Jun 17 '11 at 14:16
  • $\begingroup$ @GregSnow @Greg Snow My co worker just informed me that I want the Prediction Interval $\endgroup$ – chhhhhh Jun 17 '11 at 18:09
  • $\begingroup$ I calculated the NORMINV even though it's not what I wanted. I want to know what the values mean. I got -.135597829 and .769904. So does that mean I can claim that 95% of the true percent error lies between -.135597829 and .769904 under the conditions that our sample is a correct representation of the true population. $\endgroup$ – chhhhhh Jun 17 '11 at 18:59
  • $\begingroup$ @chhhhhh for your first question, I think we may be using the term "fit" differently. In statistics we usually talk about fitting a distribution as finding the set of parameters (mean and var/sd for normal) that make the theoretical distribution and obsevred data as close as possible (for some definition of close), the norminv function in excel does not find the mean and sd from the data, but will tell you the x value for an area under the normal when you give it a mean and sd. $\endgroup$ – Greg Snow Jun 17 '11 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.