10-31-2019 04:31 AM
10-31-2019 04:31 AM
I'm new to this forum and have the following problem to solve: I need to have Excel automatically calculate how many vials of two sizes a patient needs to get dosed. The vial sizes are 500 and 350 mg. Example: Patient requires 1780 mg, so would need 3 x 500 mg plus one 350 mg vial. What would the formula look like? - Thanks in advance for any help!
10-31-2019 08:11 AMSolution
With the help of below Nested If formulas you can achieve your requirements:
For 500Mmg =IF(MOD(B3,500)=0,B3/500,IF(MOD(B3,500)>MOD(B3,350),ROUNDDOWN((B3/500),0),1))
For 350mg (=IF(D3*500>=B3,0,IF(MOD(B3,350)=0,B3/350,IF(MOD(B3,500)>MOD(B3,350),1,ROUNDDOWN(B3/350,0))))
A sample file is also attached for your reference, hope it will help you.
In case you need some tweak in the formula please let me know.
10-31-2019 02:54 PM - edited 10-31-2019 03:29 PM
As with most problems in Excel, there are many solutions so there are likely many solutions for this problem. However, I decided to make use of Excel's Solver application to tackle your issue. If you aren't yet familiar with Solver, refer to the following link:
After setting up your problem, Solver can determine the minimum waste amount with ease. The constraints I used for your problem are as follows:
Set Objective: $B$2 to Max - we want the least negative value for waste
By Changing Variable Cells: $B$5:$B$6 - by changing the vial quantities
$B$2 >= -349 - we want the waste to be less than the minimum vial amount i.e. 350mg. Waste is marked as a negative amount hence the formula being $B$2 >= -349 and not $B$2 <= 349.
$B$2 <= 0 - the waste cannot be positive, therefore it must be less than or equal to 0.
$B$5:$B$6 = integer - we cannot fill a partial vial, so vial quantities must be integers.
$B$7 >= 1 - prescriptions must contain at least 1 vial.
$C$7 <= $B$1+350 - the total amount of mg must be less than or equal to the prescribed amount plus the minimum vial amount i.e. 350mg.
I have attached an example file for your use.
Edit: I apologize as I did not do enough testing and discovered an error in my initial file. I have attached the updated file as well as updated the necessary constraints in my reply marked in red.
11-01-2019 06:26 AM
Thank you so much for your explanations, you really got down to the problem, and I understand the Excel Solver. My problem is now: how to translate this into a formula which I can enter into an Excel cell and then have it automatically fill out the value once the set objective is amended? - Thank you in anticipation for your continued support!
11-04-2019 02:19 AM
Hi Tauqeer, there is a mistake in the formula - for values between 850 and 1000 mg, the formula gives one vial each, which is not sufficient. I am attaching the file which you had sent and added one line to demonstrate that it does not calculate correctly. Would you be so kind and have a look into it? -Thanks in anticipation, your continued support is highly appreciated!
11-04-2019 05:04 AM
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!
09-09-2020 11:22 PM
@Nedae9 The solution that I provided earlier only works for two-dimensional arrays. So, adding a third dimension is not possible. But, if you can live with a slightly more manual approach, have a look at the attached workbook. Vials is sizes 500, 350 and 100 have a common denominator of 50 (500=10x50, 350=7x50 and 100=2x50). Let's call this the minor unit. Divide the required dose by 50 and you get the number of minor units needed. From the matrix in the attached workbook it can then be fairly easily determined which combination of vials is needed, minimising waste.
Example: Dose needed = 1250 = 25 minor units: From the matrix you can create that number by 10 + 7 + 8, thus 1 x 500 + 1 x 350 + 4 x 100. Or 21 + 4, thus 3 x 350 + 2 x 100.
Now, if the dose needed is not a multiple of 50 you'll end up with some waste, but as long as the sum of minor units used is nearest to the number of units needed, the waste will always be the minimum.
I feel that it must be possible to put all of this in a mathematical model/formula, but that's not my strong point.