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.