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

posted by Helmut Homepage – Vienna, Austria, 2020-10-17 13:30 (1228 d 22:37 ago) – Posting: # 22020
Views: 3,189

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.


❝ 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 [image] on my six years old machine (CVwR = CVwT, T/R-ratio 0.975, ≥80% power, 4-period full replicate design, 100,000 simulations):

  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

[image] 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. ;-)

Edit 01 Jan 2022: Background and examples.

Dif-tor heh smusma 🖖🏼 Довге життя Україна! [image]
Helmut Schütz

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

Complete thread:

UA Flag
 Admin contact
22,912 posts in 4,806 threads, 1,637 registered users;
35 visitors (0 registered, 35 guests [including 6 identified bots]).
Forum time: 11:08 CET (Europe/Vienna)

Exploratory analysis: The art of finding a Rembrandt
in a Jackson Pollock.    Stephen Senn

The Bioequivalence and Bioavailability Forum is hosted by
BEBAC Ing. Helmut Schütz