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

posted by Helmut Homepage – Vienna, Austria, 2020-10-17 09:30 (7 d 05:15 ago) – Posting: # 22020
Views: 729

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,179 posts in 4,414 threads, 1,474 registered users;
online 18 (0 registered, 18 guests [including 12 identified bots]).
Forum time: Saturday 14:46 UTC (Europe/Vienna)

It is the peculiar and perpetual error of the human understanding
to be more moved and excited by affirmatives than negatives.    Francis Bacon

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