Annoying isn’t it that excel cannot compute d prime or standard Error ‘in one fell sweep’. The solution, write your own function 🙂
Writing your own functions
- Open up a new workbook.
- Get into VBA (Press Alt+F11)
- Insert a new module (Insert > Module)
- – Copy and Paste the Excel user defined function examples –
- Get out of VBA (Press Alt+Q)
- Use the functions (They will appear in the Paste Function dialog box, Shift+F3, under the “User Defined” category)
If you want to use a UDF in more than one workbook, you can save your functions in your own custom add-in. Simply save your excel file that contains your VBA functions as an add-in file (.xla). Then load the add-in (Tools > Add-Ins…). Warning! Be careful about using custom functions in spreadsheets that you need to share with others. If they don’t have your add-in, the functions will not work when they use the spreadsheet.
- Create a complex or custom math function.
- Simplify formulas that would otherwise be extremely long “mega formulas”.
- Diagnostics such as checking cell formats.
- Custom text manipulation.
- Advanced array formulas and matrix functions.
Useful additional stuff
By inserting the code – “Application.Volatile”, the function will re-calculate just like an in-built function. This should overcome the problem mentioned in the paragraph entitled Warning. For example:
Function Dprime(Hit As Double, Falarm As Double)
Dprime = WorksheetFunction.NormSInv(Hit) – WorksheetFunction.NormSInv(Falarm)
Example: standard error
Function StdErr(k As Range)
StdErr = (WorksheetFunction.StDev(k)) / Sqr(WorksheetFunction.Count(k))