FAQ: Statistics using Microsoft Excel
Using Microsoft Excel
|If you need a sum like SUM(A1:Ax), where x is contained in B1, use
|(Answer due to David Hager)|
|Guido Wyseure reports:
Russell Gerrard reports that the worksheet function GAMMADIST(x,alpha,beta,1) fails to return a value when x/beta is greater than alpha but less than 0.12.
|The RANK function returns the highest rank when a number values are tied, whereas most mathematical
procedures need an average value instead. To get this, suppose the data are in cells A1:A20 and that
this range has been named Range1. Array-enter in cell B1 the formula
=SUM(1*(A1>=Range1))-(SUM(1*(A1=Range1))-1)/2(You array-enter a formula by holding down Ctrl and Shift as you press Enter.) Copy-and-paste that formula into B2:B20.
|(Attributed to Bob Umlas)|
|If you give the range to be charted a name, you can have it update automatically. Suppose your current range is B5:B20. Define a name, "Myrange" to be =OFFSET($B$5,0,0,COUNT($B:$B),1) where column B contains only the numbers to chart. Then, when you put a value in B21, MyRange will automatically include it. Now use this name in your chart instead of the cell references you see in the series formula. As you add data the chart will change.|
|If the data are assumed to be in ascending order,
This is Method 1 of Freund, J and Perles, B (1987) "A New Look at Quartiles of Ungrouped Data", The American Statistician, 41, 3, 200-203.
|For a Normal with mean m, standard deviation s, use =NORMINV(RAND(),m,s).
For exponential with mean m, =-m*LN(RAND()).
For simulated gamma variables with shape parameter a and scale parameter b (in the sense that the mean is ab, variance ab2), use =GAMMAINV(RAND(),a,b).
Other distributions which can be simulated using the inverse distribution function method in Excel v5 are Beta, Chi-squared, F, logNormal and t.
|If the population is in Column A, and Column B is filled with =RAND(), then enter the formula =INDEX(A:A,RANK(B1,B:B)) in C1 and copy down to Row n, assuming a sample of size n is required.|
|You can call a macro repeatedly at a set time interval using
the OnTime method and the Call statement.
To call a macro four times at five-second intervals, follow these steps:
|(Paul Barnwell, submitted by Nigel Greenwood)|