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

     

tauqeeracma's avatar
tauqeeracma
Iron Contributor
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

 

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

    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

    • PReagan's avatar
      PReagan
      Bronze Contributor
      Oct 31, 2019

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

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

        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

    • tauqeeracma's avatar
      tauqeeracma
      Iron Contributor
      Oct 31, 2019

      Holgi You are welcome

      • Holgi's avatar
        Holgi
        Copper Contributor
        Nov 04, 2019

        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

Resources