Excel Functions for CBT

I’m currently in the process of revising the first 2 chapters of Section B (LMM). If you are already past that, the most important addition is going to be a discussion of using Excel in doing hypothesis tests. The PearsonVUE CBT format gives you a 100 row x 40 column spreadsheet for scratchwork (that carries over between problems). It is essentially a modified version of Excel, using Excel syntax for the formulas it supports, so you should practice with Excel rather than Google Sheets, which uses different syntax.

In the hypothesis tests that we will be doing, we will always care about either the right tail (likelihood ratio test with chi-square distributions, F-tests) or 2-sided tests (t distribution). Because of that, I would recommend always using the .RT or .2T versions of those functions. That is, with chi-squares, I would use CHISQ.DIST.RT(x, dof), F-test would use F.DIST.RT(x, numerator dof, denominator dof), and t-test would use T.DIST.2T(x, dof). The advantages of those is a) we don’t need to subtract from 1 and/or divide things by 2, b) the syntax is all fairly similar, being <distribution name>.DIST.<RTor 2T>(x, degrees of freedom), and c) the most common error is probably selecting the wrong tail type, and plugging in x=0 into any of those 3 formulas will output 1 for the tail type we want, so you have an easy way to double check you are using the correct formula.

Join the Conversation


  1. I’m assuming we should also use the inverse version of these formulas, as in chisq.inv.rt and f.inv.rt?

    1. I need to poke around with the Pearson spreadsheet a bit, but I would think so. I will try to make something more detailed to address this closer to the exam.

  2. Will all any exam problems require the use of these functions to solve the question? Or should the exam tables given be sufficient?

    1. It’s not clear. For the Fall 2020 sitting, they were explicit that the tables would be sufficient. For a CBT format exam, in principle every problem should go through a pilot phase before becoming live, which would mean that problems requiring new techniques in the Spring 2021 are likely pilots.

      Having said that, the most likely form of a question that they can ask that requires a spreadsheet would be to ask for the p-value for a likelihood ratio test in which the test stat under the null is a 50-50 mixture of a chi^2 with d and d-1 degrees of freedom. (I.e., we are testing removing a random effect and are eliminating d parameters). In that case, if your test stat is x, you get the answer from:

      = 0.5*CHISQ.DIST.RT(x, d) + 0.5*CHISQ.DIST.RT(x, d-1)

      and the spreadsheet reminds you of the full name of the formula as well as the syntax as you type it.

Leave a comment

Your email address will not be published. Required fields are marked *