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

posted by Helmut Homepage – Vienna, Austria, 2020-10-17 11:30 (579 d 09:08 ago) – Posting: # 22020
Views: 2,336

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.


» 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):

  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. ;-)

Edit 01 Jan 2022: Background and examples.

Dif-tor heh smusma 🖖 [image]
Helmut Schütz

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

Complete thread:

UA Flag
 Admin contact
22,085 posts in 4,629 threads, 1,566 registered users;
online 15 (0 registered, 15 guests [including 8 identified bots]).
Forum time: Thursday 20:39 CEST (Europe/Vienna)

That which is not controversial
is of no particular interest.    Johann Wolfgang von Goethe

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