Charting a cost model

[Neal Reid]

I've built a couple of cost models whch contains a number of inputs. I
would like to be able to set the models to the same values and chart the
output value over a range of input values.

For example, chart the total cost (output calculated value) for each
model for 300 - 500000 pieces. The two models are on separate sheets in
a workbook, set up such that I can enter a value in an input cell and
see the effect for that value. Currently, I generate my charts by
manually setting each model to a given value then cutting and pasting
the output into a 3rd sheet, upping the input, cutting and pasting...
until I have a representative set of values, the charting that.

I can't help but feel there has to be a better way to achieve the result
of, "Plot the value of H53 as the value of C3 is stepped from X to Y by
steps of Z"

I suspect this implies a macro. Is there another solution? If not, any
pointers on what the macro looks like?

 

[Ken Wright]

Take a look at 'Create a one-variable data table ' in Help. This allows you to
create a table based on a set of formulas (ie your cost model) that take an
input variable and create an output value. By using this feature you can create
a table of different input variables (ie your differing values of C3), and it
will use your model to generate a table of output values (ie your H53).

 

[Neal Reid]

Take a look at 'Create a one-variable data table ' in Help. This allows you
to
create a table based on a set of formulas (ie your cost model) that take an
input variable and create an output value. By using this feature you can
create
a table of different input variables (ie your differing values of C3), and it
will use your model to generate a table of output values (ie your H53).

Thanks - (almost) worked a treat...

I say almostbecause Excel appears to insist the series table be built on
the sheet in question. I tried to create 2 tables on a 3rd sheet
complete with a chart of the 2 results. I could build the tables and
specify the output value, but when I tried to select the varying input
column (either by point/click or manual typing to try alternative
formats) the wizard always return that that cell (Sheet1!C3) 'Input cell
reference is invalid'

When I build the identical table ON Sheet 1, it works fine. Am I missing
something?