Forum Discussion
Numbers calculation
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 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
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).
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
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.
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.
Holgi You are welcome
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
