Charting the relationship between two cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can you / How do you chart a the relationship between two cells (preferably
without making two tables). It feels like this should be a simple function
of excel.

I want to see how a cell is effected when I put numbers 1-1,000,000 in it.

For example, I have a worksheet that asks how many units I am managing. I
put in that number in a single cell and my worksheet comes alive telling me
how many people I need to have, how much space I need, etc, etc. But most
importantly it shows me the bottom line profit. I want to chart the
relationship between those two cells (number of units and profit).

example data:
If I have 10 units, my profit is -$200.
20 units / -$50 profit
30 units / +$40 profit
50 units / -$70 profit (because with 50 units I need to buy more space)
80 units / +$160 profit
etc.

A chart would help show how well my worksheet flows.
 
Can you / How do you chart a the relationship between two cells (preferably
without making two tables). It feels like this should be a simple function
of excel.

I want to see how a cell is effected when I put numbers 1-1,000,000 in it.
Click to expand...

The relationship between two variables is best shown by a scatter (or
"XY" chart). All you need to do is provide the chart with a pair of
columns, the left of which is the range of values you put in your input
cell, (that's number of units in this case), and the right of which is
the dependent variable, the results of your output cell. for best
results, put a text label on the top of the second column, but leave the
first column without a label. Include the label row when you select the
range for the chart wizard.

You cannot just give the chart engine the two cells and ask it to cycle
between the values. The spreadsheet is the place to do that, the chart
is just a small feature that reads columns or rows and displays them.

In general we must always remember that Excel is a large spreadsheet
program with a small graphing utility attached, not-- like the ancient
"Harvard Charts" program was-- a graphing program with a little tabular
input area. Always process your data in the spreadsheet, and never
search for the feature of the chart that will do all the calculations;
there isn't one.

(the "trend line" feature is an exception to this rule, and perhaps
unfairly raises the users' expectations because of this-- and then the
users ask how they can extract the data from the trendline to use in the
spreadsheet!)
 
There is a VBA way to plot a function without a table but I cannot recall
the reference to the URL. Try Google.
 
There are two approaches you could use.

1. Write a VBA routine that sequentially plugs a value into the first cell,
then copies this value and the result in the other cell into a blank
worksheet. Then plot the two resulting columns of numbers.

2. Use Excel's little-used and less-understood Table feature to set up a
recurring calculation. Dermot Balson shows how on his web page:

http://www.westnet.net.au/balson/ModellingExcel/LookMaNoCode.shtml

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
774-275-0064
208-485-0691 fax
(e-mail address removed)
http://PeltierTech.com/
_______
 
Back
Top