SOLVED

Numbers calculation

Copper Contributor

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

16 Replies
best response confirmed by Holgi (Copper Contributor)
Solution

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

 

clipboard_image_0.png

 

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

 

@tauqeeracma 

Hi Tauqeer,

Thanks so much, thats very helpful! Your help is very much appreciated!

Have a good rest of the day!

Kindest regards,

Holgi

@Holgi You are welcome

Hello @Holgi,

 

Are you seeking a combination of 500mg and 350mg vials such that the waste is minimized?

For example, say the prescribed amount is 700mg. Would you require a 500mg vial and a 350mg vial (150mg waste) or two 350mg vials (0mg waste).

@PReagan 

Hello PReagan,

Yes, that is the intention behind the use of two different vial sizes. Do you have a formula which would consider this? Your help would be much appreciated!

Kind regards,

Holgi

@Holgi,

 

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:

https://www.excel-easy.com/data-analysis/solver.html

 

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.

 

clipboard_image_1.png

 

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.

 

@PReagan 

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!

Kindest,

Holgi

@Holgi 

 

Is a formula necessary or would a VBA code to automatically run Solver suffice?

Hi @PReagan 

As I am not familiar with VBA codes, I would prefer a formula. Thanks in anticipation for your continued support!

Kindest,

Holgi

@tauqeeracma 

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!

Kindest

Holgi

Hi @Holgi 

 

Sure I will look into it and come back with modified and correct formulas.

 

Thanks

Tauqeer

@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!

@Riny_van_Eekelen 

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

@Riny_van_Eekelen 

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.

1 best response

Accepted Solutions
best response confirmed by Holgi (Copper Contributor)
Solution

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

 

clipboard_image_0.png

 

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

 

View solution in original post