+ Reply to Thread
Results 1 to 8 of 8

Two sized vitamin pills - how many of each at a specific dosage?

  1. #1
    Registered User
    Join Date
    12-20-2008
    Location
    Perth, Western Australia
    MS-Off Ver
    Microsoft - Office Enterprise 2007
    Posts
    28

    Two sized vitamin pills - how many of each at a specific dosage?

    I have a stock of Vitamin B1 capsules in two sizes - 100 mg and 250 mg

    How can I calculate the number of each capsules required to meet a certain dosage, say 650mg or 350mg?

    Can anyone help please?

    Regards

    Richard

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,542

    Re: Two sized vitamin pills - how many of each at a specific dosage?

    I would probably do a "trial and error" kind of approach. Starting with an algebraic statement of the problem:

    n*100+m*250=target
    n and m have to be integers.
    Solve for one of the variables (I would probably solve for n)
    Try different values for m (0,1,2,3) -- calculate n
    Solution's are found for any case where n is an integer. (m=1 and n=4 would be one solution for 650)
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,547

    Re: Two sized vitamin pills - how many of each at a specific dosage?

    I tried to do a setup with Solver but I don't know how to tune it. Alf or shg or bsalv or any other Solver-guy, are you out there?
    I even considered a macro that would run solver for each column at a time (kind of lame, yes I know ) but even there it would run thousands of sub- whatever.
    Restrictions were int, >=0 and <10 and I tried both minimising ABS and just go for zero.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,542

    Re: Two sized vitamin pills - how many of each at a specific dosage?

    If I used Solver, I would set it up as a root finding type problem:

    n*100+m*250-target=f(m,n)=0

    Enter values for n, m, and target
    Enter f in a cell
    Call Solver and tell it to set f equal to a value of 0 by changing m and n subject to constraints that m and n >=0 and integer

  5. #5
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,547

    Re: Two sized vitamin pills - how many of each at a specific dosage?

    Yup, that is pretty much what I did. And solver did a fair but not perfect jobb and it took forever. Evolutionary algorithm, it doesn't seem to accept any other for this setup. I assume the settings for this and that under options have to be tweaked as well somehow but I have no clue what they even mean.

  6. #6
    Registered User
    Join Date
    12-20-2008
    Location
    Perth, Western Australia
    MS-Off Ver
    Microsoft - Office Enterprise 2007
    Posts
    28

    Re: Two sized vitamin pills - how many of each at a specific dosage?

    I did it long hand with a vlookup table with dosages spaced at 50mg up to 1500 - seems to work in the range I need

  7. #7
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,492

    Re: Two sized vitamin pills - how many of each at a specific dosage?

    I've set up a macro running solver in a loop. There are probably better ways of setting up solver but as Sweden celebrate "Midsummer" yesterday my brain is not yet up to producing a more advanced solution.

    To test run macro "SolvLoop"

    Alf
    Attached Files Attached Files

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Two sized vitamin pills - how many of each at a specific dosage?

    Transposed for convenience:

    A
    B
    C
    D
    E
    1
    Dose
    250
    100
    2
    250
    1
    0
    B2 and down: =($A2 - C2*C$1) / B$1
    3
    300
    0
    3
    C2 and down: =MATCH(TRUE, MOD($A2 - {0,1,2,3,4} * C$1, B$1) = 0, 0) - 1
    4
    350
    1
    1
    5
    400
    0
    4
    6
    450
    1
    2
    7
    500
    2
    0
    8
    550
    1
    3
    9
    600
    2
    1
    10
    650
    1
    4


    I believe that works for any dose 250+ divisible by 50.
    Last edited by shg; 06-21-2014 at 05:52 PM.
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Three pill sizes for a specific dosage
    By thetaplus in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-04-2014, 01:50 PM
  2. How to remove dosage info from the drug name in excel?
    By nightxiao in forum Excel General
    Replies: 3
    Last Post: 03-20-2014, 07:32 PM
  3. Different sized data with dates
    By Macro-wave in forum Excel General
    Replies: 2
    Last Post: 10-21-2008, 04:39 PM
  4. 'Sized With Windows'
    By Bruce Banner in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-24-2007, 05:57 PM
  5. File sized in inexplicable way
    By Sergio Luis Martins in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-14-2006, 10:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1