Bioequivalence and Bioavailability Forum • Simulating studies in Excel possible but slow?

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

posted by Helmut – Vienna, Austria, 2020-10-17 13:30 (1456 d 23:41 ago) – Posting: # 22020
Views: 4,231

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 pseudorandom number generator was just 2¹⁶ – 1 = 65,535.
However, I guess that simulations in VBA will be extremely sloo_ow.
In [image]

on my six years old machine (CV_wR = CV_wT, 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

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 🖖🏼 Довге життя Україна!
Helmut Schütz

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

Complete thread:

Sample size for NTI using FDA method by excel macro dshah 2020-10-16 06:20 [Power / Sample Size]
- Sample size for NTI using FDA method by excel macro d_labes 2020-10-16 11:09
  - Sample size for NTI using FDA method by excel macro dshah 2020-10-16 12:11
- Simulating studies in Excel not possible Helmut 2020-10-16 11:11
  - Simulating studies in Excel not possible dshah 2020-10-16 12:12
    - Simulating studies in Excel not possible martin 2020-10-16 12:52
      - Simulating studies in Excel not possible dshah 2020-10-20 06:59
  - may be possible -> Simulating studies in Excel not possible yjlee168 2020-10-17 08:09
    - Simulating studies in Excel possible but slow?Helmut 2020-10-17 11:30
      - Have you upgraded to R v4.0.3 yet? yjlee168 2020-10-19 02:16
        
        Run on R v4.0.3 Helmut 2020-10-19 12:12