Excel - define a formula in one cell to be used in other cells with different inputs like a function

Question

I use excel 2010. I want to use a formula defined in one cell as if it was a user-defined function.

Example: In A1 I put something like (I know it's not a real syntax):

= 3 * Input + 15

And then in B1 I'll use it like this:

= A1(10)

And in C1:

= A1(20)

I'd rather do it this way and not resort to writing a VBA function. Is it possible? If so, how?

Thanks.

Answer 1

One solution is to use Excel's Data Table feature. The Data Table is one of the What-If Analysis options available (in Excel 2007 -- I can't confirm this, but I assume it's still in Excel 2010). The following is an example of how to use this feature, which should be useful because the Microsoft help file is light on instruction IMO.

To use your example, you want to calculate the formula = 3*x+15 for several different values of x. Where B1 holds some dummy value of x, enter the following in C2:

=3*B1+15

In B3 and down, enter the x values you want to test.

Now that you have the framework for your data table, you can perform a What-if analysis. Select your entire table framework (B2:C8 in this example, which includes the values and the formula). Now, on the Data ribbon, select 'What-if Analysis' -> 'Data Table...'. In the dialog, enter the address of your dummy cell to which your formula refers in the Column input field. (If you table is set up horizontally rather than vertically, you should enter the address in the row input field.)

Click OK and observe that your table is filled with the appropriate output values for each input value.

While this solution may not offer the flexibility a UDF would offer, I think it does essentially what you are asking. Your example formula is simple and can just as well be filled down, but I assume you have your reasons for wanting to use the formula in this way. Data tables are ideal for situations where you have lots of data that depend on the dummy value, and the formula you want to evaluate for different values refers to that dependent data. For instance, I've used this feature to generate ROC curves quickly by calculating sensitivity and specificity at different thresholds.

Answer 2

You might try using defined names in formulas. Once you've defined a formula you can use it by name repeatedly. This feature was added to make it easier to reuse formulas.

Here's a link to the MS Office site for how to define and use names in formulas.

Answer 3

In newer Excel functions, you can create an Excel Custom Functions Add-In! It allows you to define Node.JS functions and reuse them.

Microsoft provides a good tutorial on how to utilize yo's office generator.