FAQ: Statistics using Microsoft Excel

Association of
Statistics Specialists
Using Microsoft Excel

Index of Questions


How can I draw boxplots?

See http://www.mis.coventry.ac.uk/~nhunt/boxplot.htm Top
(Neville Hunt)

How can I create a set of statistical tables?

See http://www.mis.coventry.ac.uk/~nhunt/tables.htm Top
(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


(Answer due to David Hager)

What are the known problems with Excel?

Guido Wyseure reports:
  1. negative det. coeff. with zero-intercept regression (however, after looking in detail into the matter this is not as big a problem as some people cry out)
  2. approximations of the probability functions at low probabilities (less than 1%) are not good.
  3. numerical approximations under high multi-collinearity are not good (I tried a few difficult and very artificial regressions, Excel always gave me an answer, while SAS refused the matrix inversion and warned that the regression was meaningless).
The Report of RSS Spreadsheet Forum discussion, March 96 has a longer list.

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. Array-enter in cell B1 the formula


(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)

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. Top
(Bob Umlas)

How does Excel calculate its quartiles?

If the data are assumed to be in ascending order,
  • the lower quartile (Q1) is the ¼(n+3)th observation
  • the second quartile (median) is the ½(n+1)th observation
  • the upper quartile (Q3) is the ¼(3n+1)th observation
When ¼(n+3), ½(n+1) and ¼(3n+1) are not integers, linear interpolation is used.

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.


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


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. Top
(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 five-second intervals, follow these steps:

  1. Cut and paste the following code into a new module sheet:
          ' Module level declaration of icount, inumberofcalls.  This line 
          ' must be at the top of the module sheet 
          Dim icount, inumberofcalls As Integer
          Sub StartOnTime()
              ' Initialize icount to 1.
              icount = 1
              ' Initialize inumberofcalls to 4.
              inumberofcalls = 4
              ' Select the range of cells for formatting.
              Range("A2:A" & inumberofcalls + 1).Select
              ' Format the selected cells as time.
              Selection.NumberFormat = "h:mm:ss AM/PM"
              ' Start in cell A1.
              ' Put the word "Time" in cell A1.
              ActiveCell.Value = "Time"
              ' Start the OnTimeMacro.
              Call OnTimeMacro
          End Sub
          Sub OnTimeMacro()
              ' Run the RunEvery5seconds macro inumberofcalls times.
              If icount <= inumberofcalls Then
                  ' Run the RunEvery5seconds macro in 5 seconds.
                  Application.OnTime Now + TimeValue("00:00:05"), "RunEvery5seconds"
                  ' Increment icount by 1.
                  icount = icount + 1
                  ' Icount is greater than inumberofcalls, so exit the macro. 
                  Exit Sub
              End If
          End Sub
          Sub RunEvery5seconds()
              ' Places the current time in a cell.
              ActiveCell.Offset(icount - 1, 0).Value = Format(Now(), "hh:mm:ss")
              ' Runs the OnTimeMacro again.
              Call OnTimeMacro
          End Sub

  2. Run the macro StartOnTime.

    The macro enters the time at which it ran into cells A2, A3, A4, and A5 on Sheet1 of the active workbook.

(Paul Barnwell, submitted by Nigel Greenwood)


This page is maintained by Russell Gerrard.
Please address queries to R.J.Gerrard@city.ac.uk.