Creating a custom function in Excel

Question

It seems like such an obvious thing that excel should have this function, I just can't find it.

How to create a custom function without using VBA? (VBA is too big to hammer and causes safety warnings, etc.).

For example, I have a spreadsheet with several very complex formulas. Each of these formulas is replicated in multiple columns. Each column contains hundreds of records, so each of them is replicated hundreds of times. If I correct something, then I need to manually fill out or copy my change from one column to another.

The simple one is as follows:
= (Payment1 - F $ 12) * 12 + ($ D21-H21)
But I would like to do the following:
= MyFunction (e 12 $, $ D21, H21)
And to have the actual formula for "MyFunction" written just somewhere.

I have found several things that are approaching to give me what I want. For example, in spreadsheets, Excel automatically replicates changes in the formula down the rest of the column, saving you a manual step by selecting a range and doing “Fill Down”.

It will also allow relative references to named cells, which is equivalent to user parameters without functions.

Answer 1

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

In cell A2, create the name EvalAbove , and in the Refres To field, enter =evaluate(A1)

So you can build a formula for example B1 contains SUM , B2 contains =("="&B1&"(A2:A5)")
and in B3 you can put =EvalAbove

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

• Note that this is still considered a macro-enabled entry, but there is no VBA code, only named ranges

Answer 2

You can do this for the example you are showing, if I interpret it correctly. If not, you can change things a bit to fit

your function has three parameters:

The first comes from row 12 of the current column. The second comes from column D of the current row. The third comes from column two to the right of the current row. I assume that Payment1 is already a named variable?

Place the cursor on line F21 and then define this name

MyFunction = (Payment1 - F $ 12) * 12 + ($ D21-H21)

This will result in settings from the locations shown.

To understand this better, switch to RC mode and enter the formula as:

  =(Payment1 - R12C)*12 + (RC4-RC[+2]) 

Now you can distribute the formula through F coloumn

= My_function

and it will always use the values ​​in the corresponding column F12 Dxx and column Hxx

If you drag the formula to the next column, it will use G12, Dxx and Ixx

If you want to change the formula, edit it in the namespace

This is a general exception to the rule in which you cannot use UDF without VBA in Excel. Often in Excel, the things you want to use as the “arguments” for a function are actually in fixed places (rows or columns) that you can relate to.

For example, you often want to execute udf in the cell on the left

Thus, udf, giving the cuber of the cell to the left, will be a named formula:

Cuberoot = (RC [-1]) ^ (1/3)

Or in form a1, position the cursor in B1 and type = (A1) ^ (1/3) And Excel will convert it internally to RC form

For three arguments - use three columns

It works and does not suffer from the volatility problem mentioned in the evaluation question ()

Yes, I know this is an old post, but it can help someone with the same problem.

Bob J.