Numbers calculation

Holgi 

Since you indicate that you want a formula that automatically generates the most efficient (i.e. least waste) combination of vials, I thought of a solution that I would like to share with you. It works with a clever use of the SUMPRODUCT function that I read about on the web in articles by Mike Erickson (MrExcel.com) and Dave Burns (ExcelJet.net) . I could never have made this up myself 🙂

First I created a matrix that calculates the total quantity of all possible combinations of ten 500mg vials and nine 350mg vials. Secondly, I calculated the waste for each combination, based on the dose needed (=cell O32). 

All you need to do is enter the required dose in cell "O32". The most efficient combination of vials is calculated in Q32 and R32 by the SUMPRODUCT function. Now, I learned that this trick only works if the array that this function evaluates contains ONLY unique values. I started off with a 10x10 matrix but had to decrease to 10x9 to avoid such duplicates. This sets the maximum dose to 8150mg (10x500 plus 9x350).

The attached workbook displays all of these calculations. Obviously, you don't want to display everything all the time. Just press the grouping-buttons in the top and on the left to collapse the "unwanted" columns and rows.

I hope you find this useful and that it works for you. If not, then I at least learned something myself. Thanks!