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

posted by Helmut Homepage – Vienna, Austria, 2020-10-17 13:30 (1679 d 03:31 ago) – Posting: # 22020
Views: 5,648

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

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

Complete thread:

UA Flag
Activity
 Admin contact
23,424 posts in 4,927 threads, 1,681 registered users;
49 visitors (0 registered, 49 guests [including 32 identified bots]).
Forum time: 17:02 CEST (Europe/Vienna)

Pharmacokinetics may be simply defined as
what the body does to the drug,
as opposed to pharmacodynamics, which may be defined as
what the drug does to the body.    Leslie Z. Benet

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