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:

enter image description here

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:

enter image description here


  • 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


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy