Plot result from various input in one cell affecting other cells

**daolo** · 12-10-2014, 11:05 AM

Hello!

Don't know how to handle this issue (in fact I'm very inexperienced when it comes to charts).

I have one input cell where I enter a production number (number of units produced).

Several other cells use/refer to this number/input in different formulas together with static input like price and size of batch.

Now I'd like a chart where I plot values from the input cell (say from 1 to 5000) on one axis, and on the other axis I plot the result from one of the other cells using the input cell to calculate a cost.

Is this possible? If not - what would be the easiest way to work around this? My goal is to easily be able to plot where the production number is most effective - which number of produced units corresponds to the lowest cost per unit).

(Since the result cell has dependencies from other cells like cost and volume, I just can't separate it to get two colums with input and output and then plot them...)

Hope you understand my issue..!

Thank you in advance...:!

**MrShorty** · 12-10-2014, 11:22 AM

I fully expect it is possible to do this. Exactly how to approach it will depend on other constraints to the problem (how much you are willing to edit the spreadsheet, for example). A sample spreadsheet may help us give you more specific suggestions.

My usual approach to this kind of problem looks something like this in the spreadsheet:

Please Login or Register  to view this content.

The chart is then created based on column 1 and the final result column. Of course, this only works if you are able and willing to edit your spreadsheet so a given calculation can fit on a single row.

**daolo** · 12-10-2014, 11:31 AM

Ok, attaching my file.

Quite many dependencies...! Sample.xlsx

Cell F2 is the number I want to iterate.

Sum of column T would be the other axis.

**MrShorty** · 12-10-2014, 11:59 AM

My suggestion would then be to "move" F2 down to B6. Additional values for "units to produce" will be entered down column B. Adjust references in L6 and R6 so that they refer to B6 (note relative reference), then copy these formulas down for as many entries in column B that you decide you want. The chart is then created using column B as the horizontal/Xvalues/category axis with column T as the vertical/Yvalues axis.

**daolo** · 12-11-2014, 03:03 AM

Originally Posted by MrShorty

My suggestion would then be to "move" F2 down to B6. Additional values for "units to produce" will be entered down column B. Adjust references in L6 and R6 so that they refer to B6 (note relative reference), then copy these formulas down for as many entries in column B that you decide you want. The chart is then created using column B as the horizontal/Xvalues/category axis with column T as the vertical/Yvalues axis.

Problem is that production consists of maybe 50 components. And my chart should plot units to produce vs totalt cost of components from column R or S...
Suggestions?!

**MrShorty** · 12-11-2014, 12:08 PM

I'm not sure I'm understanding. Your example sheet suggests that you want to chart one input variable against 2 output variables. Are you now saying that you want to extend this to 50 input variables? If that is the case, it should be obvious that charting 50 dimensions of data on a 2D screen/paper is rather difficult. My own attempts at this are almost always about reducing the number of variables, then plotting the few most important variables in some kind of contour plot.

If this is mostly an optimization problem, is it really necessary to plot the results? A well designed chart can help understand and visualize a function, but, especially one in 50D, it might be easier to solve the optimization numerically (using something like Solver, maybe, or a Monte Carlo algorithm, or something else), and not worry about visualizing the problem.

**daolo** · 12-12-2014, 03:48 AM

Originally Posted by MrShorty

I'm not sure I'm understanding. Your example sheet suggests that you want to chart one input variable against 2 output variables. Are you now saying that you want to extend this to 50 input variables? If that is the case, it should be obvious that charting 50 dimensions of data on a 2D screen/paper is rather difficult. My own attempts at this are almost always about reducing the number of variables, then plotting the few most important variables in some kind of contour plot.

If this is mostly an optimization problem, is it really necessary to plot the results? A well designed chart can help understand and visualize a function, but, especially one in 50D, it might be easier to solve the optimization numerically (using something like Solver, maybe, or a Monte Carlo algorithm, or something else), and not worry about visualizing the problem.

Input is only one - units to produce.

Output would be sum of cost of all components included in chosen production (sum of column T). (Number of components are the same regardless of input, price differs though,.)

I would want to run this calculation from 1 unit to produce up to for example 1000 units to produce(X-axis) - and plot the total cost of all the components on the y-axis.

**MrShorty** · 12-12-2014, 09:20 AM

I would want to run this calculation from 1 unit to produce up to for example 1000 units to produce(X-axis) - and plot the total cost of all the components on the y-axis.

My suggestion is to put each calculation in its own row. With the correct combination of relative and absolute references, it should be fairly easy to create as many copies of the calculation as you need.