Worksheet as custom function?

**burro** · 11-10-2011, 05:24 PM

Hi --

I (newbie) have giant worksheet (in Excel 2003; 10 column x 50,000+ rows).

Basically, all it does is to take two input cells, and yield results in four other cells.

In other words, changing the two input cells changes almost everything else on the worksheet, and the results are displayed in four cells at the bottom of the worksheet.

Now I need to get results for 50+ different input scenarios, and post them all on a summary tab:

InputA1 InputA2 OutputA1 OutputA2 OutputA3 OutputA4
InputB1 InputB2 OutputB1 OutputB2 OutputB3 OutputB4

Etc.

Is there a way to turn my entire existing worksheet into a single custom function (or set of four custom functions, one for each output), so that if I use InputA1 and InputA2 as the input variables, I can get the outputs for that scenario?

Then my summary tab would have a table of one row per scenario:

InputA1 InputA2 OutFcn1(InputA1,InputA2) OutFcn2(InputA1,InputA2) OutFcn3(InputA1,InputA2) OutFcn4(InputA1,InputA2)

Is this possible?

It doesn't look like a pivot table or a vLookUp -- is it something else that is easy to do?

I am stuck due to my limited knowledge and must get this sorted out fast -- Many Thanks!

**shg** · 11-10-2011, 06:48 PM

You could use a 2D data table (actually, four 2D data tables, one for each output).

I can't imagine why you need 500,000 cells to process two inputs into four outputs, though.

**burro** · 11-10-2011, 07:40 PM

Originally Posted by shg

I can't imagine why you need 500,000 cells to process two inputs into four outputs, though.

Thanks.

The inputs basically select a time-frame -- start date and end date -- on a massive amount of time-series data.

The outputs depend on the data in the specified time-frame.

[But determination of the output is a little complex. For example, data in one column will be summed only once a running total in another column reaches a certain threshold, etc.]

Anyway -- I've got the algorithm worked out to correctly generate outputs once the two input dates frame the time period.

What I need is to be able to build a table of the two input dates and four output values for many different input date pairs.

I could fill out such a table by hand, copying over the four output after inputting each pair of input dates, but that is tedious and error-prone.

So I'd like some way to treat the whole worksheet as a function or set of four functions:
e.g.
OUTPUT1(date1,date2)
OUTPUT2(date1,date2)
OUTPUT3(date1,date2)
OUTPUT4(date1,date2)
or
OUTPUT1(date1,date2)=OUTPUTS(1,date1,date2)
OUTPUT2(date1,date2)=OUTPUTS(2,date1,date2)
etc.

That way, each row in my scenarios table, with one row per scenario (i.e. per pair of date inputs) would be:

date1 date2 OUTPUT1 OUTPUT2 OUTPUT3 OUTPUT4

where the outputs are functions of the dates, and the outputs are generated by the existing worksheet.

Does that make more sense?

**shg** · 11-10-2011, 08:14 PM

The data table solution would look like this:

Please Login or Register  to view this content.

The dates in row2 would get substituted into one cell, the dates in col B into another cell, and the result for that combination returned to the body of the table.

Since the table only shows one result, you'd need four tables.

This is a built-in capability of Excel. See Help for Calculate multiple results with a data table