Forum Discussion

Holgi's avatar
Holgi
Copper Contributor
Oct 31, 2019
Solved

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 a...
Numbers from two groups
  • tauqeeracma's avatar
    tauqeeracma
    Oct 31, 2019

    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

     

Nedae9's avatar
Nedae9
Copper Contributor
Sep 09, 2020

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! 

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Sep 10, 2020

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.

Resources