Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
Great job tagging and explaining what you are trying to do. –  CharlieRB May 30 '12 at 17:32

2 Answers 2

up vote 3 down vote accepted

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.

data table setup

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

what-if data table dialog

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

output

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.