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

posted by Helmut Homepage – Vienna, Austria, 2020-10-17 11:30 (288 d 11:01 ago) – Posting: # 22020
Views: 1,707

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 [image] 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 [image] 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

Complete thread:

 Admin contact
21,596 posts in 4,516 threads, 1,532 registered users;
online 5 (0 registered, 5 guests [including 3 identified bots]).
Forum time: Sunday 22:32 CEST (Europe/Vienna)

Sit down before fact as a little child,
be prepared to give up every conceived notion,
follow humbly wherever and whatever abysses nature leads,
or you will learn nothing.    Thomas Henry Huxley

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