User defined function based upon page of calculations

[Squash_Pro]

Excel Help seems to indicate that a user-defined function can only be based upon the calculations that can be placed in a single cell. If you have too many calculations to put them into a single cell, e.g., an entire page of calculations based upon a few starting parameters that eventually yield a single value, then how do you reuse this entire page of calculations? Is there another Excel mechanism that allows an entire page of calculations to used as a stored procedure?

[shg]

A user-defined function can accept single or multiple (or no) arguments, just like built-in functions. A UDF, like built-in functions, can only return values to the cells that call it.

Some functions (e.g., MIN) return a single value. Some (e.g., LINEST) return multiple values; to see them all, you have to enter it as an array formula across an appropriate number of cells.

I don't understand the rest of your question, but you can write a sub (which is not a function) that does anything and everything you can do with a bunch of formulas. A sub can modify any or all cells in the workbook (or any other workbook it can access). Such a sub cannot be invoked by a function called from a cell (i.e., a sub doesn't offer a method to get around the limitations of a UDF).

[lecxe]

Excel Help seems to indicate that a user-defined function can only be based upon the calculations that can be placed in a single cell. If you have too many calculations to put them into a single cell, e.g., an entire page of calculations based upon a few starting parameters that eventually yield a single value, then how do you reuse this entire page of calculations? Is there another Excel mechanism that allows an entire page of calculations to used as a stored procedure?

Hi Squash_Pro

I'm not sure I understood correctly, but it seems that you built a model and now you want to test it against several values of its input parameters.

Let's say you build a financial model and one of the input parameters is inflation, for which you used the value 3%. Now you'd like to know what would be the result yielded by your model for the values of inflation between 2% and 4% with increments of 0.1%. If your model involves several calculations in a group of cells it may not be practical to replicate all the calculations for each of the values of the input.

Excel has a tool, Data Table, that will do it for you. It will use your model and do all the calculations for a set the values of the input and place the respective results in a table. I believe this was what you meant by "an entire page of calculations to be used as a stored procedure". You define the calculations once and then reuse them for several values of the input.

Data Table can be used to get the result of your calculations for different values of 1 or 2 inputs.

- In excel 2007 it is in the Data tab in the ribbon, in the Data Tools section, button "What if scenarios"
- In previous version you can access it in the menu Data->Table

HTH
lecxe

[Squash_Pro]

The data table sounds like it may be exactly what I need. I'll give it a try. Thanks to all who responded. Consider this one solved.