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

posted by Helmut Homepage – Vienna, Austria, 2020-10-17 13:30 (1258 d 09:37 ago) – Posting: # 22020
Views: 3,273

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,957 posts in 4,819 threads, 1,636 registered users;
94 visitors (0 registered, 94 guests [including 6 identified bots]).
Forum time: 22:08 CET (Europe/Vienna)

Nothing shows a lack of mathematical education more
than an overly precise calculation.    Carl Friedrich Gauß

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