Creating a table using a spreadsheet as a function

**whk051** · 11-12-2012, 10:42 PM

Example.xlsx

Hey Everyone,
I am not sure I worded my title correctly, but here is my question. It's probably pretty easy but I haven't figured it out and since I am not sure how to word what I want to do, googling it hasn't helped.

I have a spreadsheet where cell A1 is a numeric input (user can enter between 1 and 10000). Based on that input, the spreadsheet does some extensive calculations then in cell B1 the result is displayed.

I would like to chart how B1 changes relative to what a user puts into A1. Now, I can create a data table using a macro that manually copies and pastes values from the first column of the table to A1 and copy and paste the result into a second column from B1, then chart off that table but that is clunky and not very elegant. Is there a simpler solution?

Thanks for helping a forum newbie

- Ralph

**Ace_XL** · 11-13-2012, 04:11 AM

Why not use a single formula..

=AVERAGE(SQRT(SUM(UserInput,UserInput*454/23))*UserInput,SUM(UserInput,UserInput*454/23),UserInput*454/23)

Also, see Column B & C of attached. Is this what you are after?

**whk051** · 11-13-2012, 10:13 AM

Ace_XL,
Thank you but the sample I uploaded was a very simple sample. The actual spreadsheet has an entire page of inputs and calculations so I unfortunately can't summarize the calculation on one line or column. I just created the simple sample so someone could show me the process. The actual calculations in my sample are meaningless.

- Ralph

**MrShorty** · 11-13-2012, 10:34 AM

A couple of strategies I've used:

1) Multiple copies of the spreadsheet tab with a "master" summary tab. Column A of the summary tab could be your main input section, A1 in each copy of the calculation tabs would link to the appropriate row in column A of the master tab. Each row of column B in the master tab links to B1 in each calculation tab. This is probably the easiest approach, especially if this is a one time project, or something you will do infrequently. I do recognize that many will feel that this is "inelegant" but you have to remember that memory is cheap these days so having multiple copies of the same spreadsheet is not necessarily a bad thing.

2) Create a user-defined function (in VBA or other programming language). The UDF can then be called like any of the built in functions -- column A contains the input values, and column B contains =myfunction(A1). I use this approach for tasks that I will do frequently in a variety of contexts/spreadsheets. The difficulty level of this approach depends on how readily you (the programmer) can take the calculation spreadsheet and reduce it to a series of VBA statements.