Create a custom function in Excel

Question

This seems like such an obvious thing that excel must have this feature, I just can't find it.

How can I create a custom function without using VBA? (VBA is too big of a hammer and causes security warnings, etc).

For example, I have a spreadsheet with several very complex formulas. Each of these formulas are replicated in several columns. Each column has hundreds of entries, so each one is replicated hundreds of times. If I tweak something then I have to manually fill-down or copy my change from one column to another.

A simple one looks like this:
=(Payment1 - F$12)*12 + ($D21-H21)
But what I'd like to do is:
=MyFunction(f$12,$D21,H21)
And have the actual formula for "MyFunction" written just once someplace.

I've found a few things that come close to giving me what I want. For example, in tables Excel will automatically replicate changes in a formula down the rest of the column saving you the step of manually selecting the range and doing a "Fill Down".

It will also allow relative references off of named cells, which seems equivalent of a user-defined parameter-less functions.

Answer 1

if you can use text to create the formula, then you can define a name to evaluate the function.

In cell A2, create a name EvalAbove, and in Refers To, enter =evaluate(A1)

This way, you can construct a formula
e.g. B1 contains SUM, B2 contains =("="&B1&"(A2:A5)")
and in B3, you can then put =EvalAbove

This means that if you change the formula name in B1, then B2 will change to show the changed formula, and B3 will change to show the result.

• Note that this still counts as a macro enabled workbook, but there's no VBA code, just named ranges