FAQ: Statistics using Microsoft Excel

=SUM(INDIRECT("A3:A"&B1))

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

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.

=SUM(1*(A1>=Range1))-(SUM(1*(A1=Range1))-1)/2

• 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

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 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²), 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.

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.
             Range("A1").Select
   
             ' 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
   
             Else
   
                 ' 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.