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

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

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,224 posts in 4,427 threads, 1,482 registered users;
online 5 (0 registered, 5 guests [including 3 identified bots]).
Forum time: Tuesday 21:15 CET (Europe/Vienna)

The fundamental cause of trouble in the world today is
that the stupid are cocksure
while the intelligent are full of doubt.    Bertrand Russell

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