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.

Leave a comment

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