FAQ: Statistics using Microsoft Excel 
Association of Statistics Specialists Using Microsoft Excel 
How can I draw boxplots?  

See http://www.mis.coventry.ac.uk/~nhunt/boxplot.htm  
(Neville Hunt)  
How can I create a set of statistical tables?  

See http://www.mis.coventry.ac.uk/~nhunt/tables.htm  
(Neville Hunt)  
How can I sum a variable range of values?  

If you need a sum like SUM(A1:Ax), where x is contained in B1, use
=SUM(INDIRECT("A3:A"&B1)) 

(Answer due to David Hager)  
What are the known problems with Excel?  

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. 

How do I get average ranks for rank correlation and rank sum test?  

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. Arrayenter in cell B1 the formula
=SUM(1*(A1>=Range1))(SUM(1*(A1=Range1))1)/2 (You arrayenter a formula by holding down Ctrl and Shift as you press Enter.) Copyandpaste that formula into B2:B20. 

(Attributed to Bob Umlas)  
How can I make a range expand automatically?  

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.  
(Bob Umlas)  
How does Excel calculate its quartiles?  

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, 200203. 

How can I simulate data from a Normal or other continuous distribution?  

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 ab^{2}), use =GAMMAINV(RAND(),a,b). Other distributions which can be simulated using the inverse distribution function method in Excel v5 are Beta, Chisquared, F, logNormal and t. 

How can I sample without replacement from a given finite population?  

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.  
(Simon Hoyles)  
How can I call a macro repeatedly at fixed time intervals?  

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 fivesecond intervals, follow these steps:


(Paul Barnwell, submitted by Nigel Greenwood)  