Solved

# How do you make a graph in Excel by varying the value of a certain cell over a certain domain?

Posted on 2011-10-10
235 Views
In my Excel spreadsheet, I have one cell where you input an arbitrary value, multiple large tables of calculations, and then one cell that contains the final result, which ultimately depends on what is in the input cell.

I would like to make a graph that shows how the Result cell varies depending on the Input cell.

For example, I would like Excel to plot "Result vs. Input" over a domain of Input from 0 to 1000 in increments of 1.

I know I could do this manually by manually calculating a table with the values of Input and the values of Result for every integer between 0 and 1000, but that seems like a tedious process that Excel should be able to automate.

How could I achieve my objective?
0
Question by:sffc

LVL 3

Expert Comment

Hi sffc,
if i am understanding the problem correctly then you need to run the input values from 0 to 1000 and based upon the calculations each value will generate a specific output in a cell for each input.

This cannot be done through formula but can be easily done through macro.

you can use the same range for your chart also. Demo.xls
0

LVL 41

Accepted Solution

@sffc - you can use a data table to simulate what you desire, without the need for VBA code.  See links to examples on how to do that...
http://www.homeandlearn.co.uk/excel2007/excel2007s7p1.html
http://www2.gsu.edu/~dscaas/pptdsc/ExcelDataTables.pdf
http://office.microsoft.com/en-us/excel-help/create-a-one-variable-data-table-HP005199545.aspx (Excel 2003)

Reading the links can make one feel that the process is quite complicated, but its actually quite simple.  Create your calculation depending on the one input variable.  Then create all possible input variables (in this example 0-1000) along column A.  Put the result (just reference the results of the calculation) on column B, one row higher than the list of input variables.  Finally, select the 0-1000 inputs and one column higher, for a 2-column selection.  Then, select (Excel 2007+ use What-if/Data Table, Excel 2003 use DATA->TABLE).  Then, because we've created a columnar list, we need to input the location of the input variable in our 1-variable datatable in the COLUMN input, leaving the ROW input alone.  Then hit OK.  Your data table should then be populated...

Attached, please find 0-1000 inputs, a "massive calculation" result and single input cell.  After having created the data table, the results are populated for each of the input variables in Column A.

What's neat about this, is you can change your "massive calculation" which could be HUGE - contain many operations, etc., and your data table will display the results (as fast as it takes to calculate!)

See attached example. dataTable-Example.xls

Dave
0

LVL 1

Author Comment

Dave,

Wow, this is just what I was looking for!  I never even realized that Excel had this feature!

This is perfect, because I was hoping that I wouldn't have to use VBA code because I prefer not to run macros when there is a formula-based or built-in feature that also achieves my objective.

Thank you very much for your time and assistance! :-)

-sffc
0