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
Medium Priority
Last Modified: 2013-11-05
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?
Question by:sffc

Expert Comment

ID: 36946813
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.

I am uploading a Demo file for your reference.

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

Accepted Solution

dlmille earned 2000 total points
ID: 36947193
@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://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


Author Comment

ID: 36947261

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! :-)


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question