Forum Discussion
Numbers calculation
Hi all,
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!
Kind regards
Holgi
Hi Holgi
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.
Thanks
Tauqeer
16 Replies
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!
This was really helpful as I am working on a similar project. Am I able to add another vial size? Ex: If a drug come in 500,350, and 100 mg?
Thanks in advance!
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.
Hi Riny,
Thanks so much, this is extremely helpful for me - the waste calculation is very important and it seems to work great with your matrix!
Kindest regards,
Holgi
Hi Holgi
Ideally, solutions suggested by Riny_van_Eekelen deserves to be marked as 'Best Response'.
Thanks
Tauqeer
Hi Holgi
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.
Thanks
Tauqeer
Hi Tauqeer,
Thanks so much, thats very helpful! Your help is very much appreciated!
Have a good rest of the day!
Kindest regards,
Holgi
