## Simulating studies in Excel possible but slow 🐌 [Power / Sample Size]

Hi Yung-jin,

» […] In MS-Excel or MS-Word, there is a macro language called VBA (Visual Basic for Applications) that is similar to any programming language.

I know, used it ages ago myself though never for statistics.

» So you just save the final results when doing simulations. Therefore, I don't think you need so many cells save each simulation, if I understand what you said correctly.

Right.

» Ps. Here is a very nice slide file about MS-Excel simulations.

You need Excel 2010 or later which uses the Mersenne Twister. In earlier versions the period of the pseudo­random number generator was just 216 – 1 = 65,535.
However, I guess that simulations in VBA will be extremely slooow.
In on my six years old machine (CVwR = CVwT, T/R-ratio 0.975, ≥80% power, 4-period full replicate design, 100,000 simulations):

library(PowerTOST) system.time(   cat("Sample size:", sampleN.NTIDFDA(CV = 0.1, print = FALSE,                                       details = FALSE)[["Sample size"]],       "\nRuntime (seconds):\n") ) Sample size: 18 Runtime (seconds):    user  system elapsed    0.14    0.01    0.15

Beat It.

For paranoiacs: Ever heard of macro viruses? I know of one company not allowing FARTSSIE
BTW, in v2.5 of last Tuesday Dave removed all reference-scaled methods and gives the code of PowerTOST in a box.

Dif-tor heh smusma 🖖
Helmut Schütz

The quality of responses received is directly proportional to the quality of the question asked. 🚮
Science Quotes