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.

On Thu, 8 Mar 2007, in microsoft.public.excel.charting,
Andrew said:
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.

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

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

There is a VBA way to plot a function without a table but I cannot recall
the reference to the URL. Try Google.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Del Cotter" wrote in message
...
On Thu, 8 Mar 2007, in microsoft.public.excel.charting,
Andrew said:
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.

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

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

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/Mod...MaNoCode.shtml

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
774-275-0064
208-485-0691 fax

http://PeltierTech.com/
_______


"Andrew" wrote in message
...
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.