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

posted by Helmut Homepage – Vienna, Austria, 2020-10-17 11:30 (183 d 14:29 ago) – Posting: # 22020
Views: 1,397

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 [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):

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


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

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

Complete thread:

Activity
 Admin contact
21,419 posts in 4,475 threads, 1,509 registered users;
online 10 (0 registered, 10 guests [including 4 identified bots]).
Forum time: Monday 02:00 CEST (Europe/Vienna)

There is one certainty in drug development
and statistics that one can depend on:
the data are always late.    Scott Patterson and Byron Jones

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