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

posted by Helmut Homepage – Vienna, Austria, 2020-10-17 13:30 (843 d 03:23 ago) – Posting: # 22020
Views: 2,682

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
22,485 posts in 4,710 threads, 1,603 registered users;
18 visitors (0 registered, 18 guests [including 4 identified bots]).
Forum time: 15:54 CET (Europe/Vienna)

The difference between a surrogate and a true endpoint
is like the difference between a cheque and cash.
You can get the cheque earlier but then,
of course, it might bounce.    Stephen Senn

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