Creating a line graph in excel with changing/result cells

For a project we are creating care packages for the homeless. I am trying to figure how many kits to make for the least leftover material as the things we are putting into the packages are sold in different units/case. I have every item filled into a sheet in excel with formulas that take the changing value, the number of kits, and runs through a bunch of calculations in multiple different cells, eventually ending up in the result cell, the cost, rounded to the nearest case. Photo for clarity: B1 is my changing cell and B4 is my result.

Now my problem arises when I want to create a line chart that shows the cost to make different number of kits. I would like the X axis to be 50-200 and the Y axis to be the theoretical cost(cost per kit * number of kits) and the real subtotal(cost per kit to the nearest case of each item). Could I do this? and how?

I don't have one singular formula to calculate the real subtotal as there are so many different items coming in different cases and prices.

• You chart data rather than a formula. You would need to build a table of the X and Y values (derived from your existing formulas), and then chart the result. – fixer1234 Aug 11 '15 at 4:02
• As fixer1234 wrote, you need a table to be able to plot the chart. Are only the highlighted cells calculated in your sheet. Most important: is "units per kit" fixed across scenarios? – Máté Juhász Aug 11 '15 at 4:36
• Everything that is highlighted in yellow is calculated. "Units per kits" is fixed across scenarios. In fact, the only thing I am changing is the number of kits – user481983 Aug 11 '15 at 5:46

To chart real vs theoretical cost, first you need to calculate them against the number of kits: Formulas:

• Theoretical cost: `=A21*SUMPRODUCT(\$E\$6:\$E\$16,\$D\$6:\$D\$16)`
• Real subtotal: `=SUMPRODUCT(ROUNDUP(A21*\$E\$6:\$E\$16/\$C\$6:\$C\$16,0),\$B\$6:\$B\$16)` - this is an array formula, so you need to finish it by CTRL+SHIFT+ENTER
• I've included also a "real / theoretical" cost column, which just divide real subtotal by theoretical cost, so it gives you also the information how much "extra" you spend over real value of the kits
• NOTE: input range is the same as posted in the question

Chart:

• instead of a single chart, I suggest to use pivot chart, this enable also filtering of input data (zoom to specific range to see costs better)