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

(some text originates from this site and this site)

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

### Benefits of User Defined Excel Functions

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

Application.Volatile

### Example: d’

Function Dprime(Hit As Double, Falarm As Double)

Dprime = WorksheetFunction.NormSInv(Hit) – WorksheetFunction.NormSInv(Falarm)

End Function

### Example: standard error

Function StdErr(k As Range)

StdErr = (WorksheetFunction.StDev(k)) / Sqr(WorksheetFunction.Count(k))

End Function