Statistical worksheet functions hint hint n n n
- Slides: 8
Statistical worksheet functions (hint, hint) n n n n Average(a 1: a 3) [average(data 1)] Stdev(a 1: a 3) [stdev(data 1)] Var(a 1: a 3) [variance] Stdevp(a 1: a 3) [stdevp(data 1)] Sqrt($a$1) [sqrt(a 1)] ^2 [to the power] Sum(a 1: a 3) [sum(data 1)] pearson(a 1: a 3, b 1: b 3) [pearson(data 1, data 2)] Rsq(a 1: a 3, b 1: b 3) [rsq(data 1, data 2)] Quartile(a 1: a 20, 0. 25) [quartile(data 1, 0. 5] Mode(a 1: a 20) [mode(data 1)] Normdist(a 1, mean, std, TRUE) Norminv(p, mean, std) Tdist(a 1, 8, 2) [students t-distribution] Tinv(p, 8) [inverse of t-distribution] Ttest(data 1, data 2, tails, type) [comparing two means] probability of rejecting null hypothesis (if this is less than significance level, reject null hypothesis). Type should equal 2.
Analysing the Gaussian (normal) distribution n The function normdist has the following prototype: n NORMDIST(x, mean, std, cumulative) n It returns the value of a Gaussian distribution with n n given mean and standard deviation at x. Cumulative is either set to `FALSE’ or `TRUE’ If it is set to FALSE the function will return the actual value of the Gaussian distribution at x. If it is set to TRUE the function will return the cumulative distribution at x (i. e. this is the table from lecture 3). Confidence level of a given interval. Eg what is the probability of a value lying in the interval. . .
Analysing the Gaussian (normal) distribution n The function norminv has the following n n prototype: NORMINV(p, mean, std) It returns the x value associated with the cumulative probability of p Useful for assessing levels of significance. Eg what are the limits on x at a given confidence level? This method is used more frequently for the tdistribution
Analysing the tdistribution n n n n You still need to remember the formulas for (1) estimating the interval for the mean; (2) testing the significance of the correlation coefficient; and (3) if two means are equal. The function tdist has the following prototype: TDIST(x, df, tails) It returns the significance level (alpha) of a t-distribution with given degrees of freedom. Tails is either set to 1 or 2. If it is set to 1 the function will return the accumulation of probability from infinity to x. If it is set to 2 the function will return the accumulation of probability in both tails. Not used too often
Analysing the tdistribution n The function tinv has the following prototype: n TINV(alpha, df) n It returns the critical value for the t-distribution corresponding to a significance level, alpha. n By default it is a two tailed confidence level, but for a one tailed confidence level substitute 2 x(alpha) for alpha. n Used in hypothesis testing.
Excel has a quick way of comparing two means: n The student t-test. n If they have the same length we can use the TTEST function n But rather than giving us the critical t-value, it gives us a critical probability for rejection. n What if this is less than the significance value, alpha? Rejection Acceptance T-critical We reject the null hypothesis
Random number generation n This is very useful in computational science. n They are not really random numbers, they are generated by an algorithm. n But it is difficult to get random numbers on a computer. n The worksheet function rand() generates random numbers between 0 and 1. n Hence to generate a normally distributed random number sequence, with a given mean and standard deviation we can use: n Norminv(rand(), mean, std) n Over many generations, the variable will have the given mean and standard deviation
Random number generation Over a large number of generations, the mean approaches that of the true random variable
