sum of multiples of 2 variables

[leeayotte]

Trying to set up a formula that will determine what combination of two capsule strengths can be used to make a final dosage.
Capsule strengths are 150 and 500 mg.
final dose can range from 150 to 4000mg
Thanks

[NBVC]

If you have 150 in A2 and 500 in B2 and you enter dosage in D2, then for 500mg capsules: =INT(D2/B2) and for 150 mg capsules: =INT((D2-B2*INT(D2/B2))/A2)

[leeayotte]

Yes I thought of that but it does not optimize.
With these two capsule strengths you can make every 50mg strength increment. By using the formula you supplied it will underestimate or overestimate depending where the dose falls and can give a non optimized solution.

[NBVC]

If you only have the 2 dosages you mentioned.. then you would first want to find out how many of the larger dosage you need, then for the remainder, you find how many of the smaller dosage will be needed...

So not sure what you mean... can you give examples of dosages and expected results, and why?

[leeayotte]

Once the dose for the patient is calculated I have it banded to give a dose based on 50mg increments which is always possible with combinations of these two strengths (except at doses less than 900mg)
Using the formula "150 in A2 and 500 in B2 and you enter dosage in D2, then for 500mg capsules: =INT(D2/B2) and for 150 mg capsules: =INT((D2-B2*INT(D2/B2))/A2)" it would tell give you to give #3 500mg for a 1600mg dose instead of optimizing it to #2 500mg capusles with #4 150mg capsules to get the exact 1600mg dose.
=IF(A10>=3626,3650,IF(A10>=3576,3600,IF(A10>=3526,3550,IF(A10>=3476,3500,IF(A10>=3426,3450,IF(A10>=3376,3400,IF(A10>=3326,3350,IF(A10>=3276,3300,IF(A10>=3226,3250,IF(A10>=3176,3200,IF(A10>=3126,3150,IF(A10>=3076,3100,IF(A10>=3026,3050,IF(A10>=2976,3000,IF(A10>=2926,2950,IF(A10>=2876,2900,IF(A10>=2826,2850,IF(A10>=2776,2800,IF(A10>=2726,2750,IF(A10>=2676,2700,IF(A10>=2626,2650,IF(A10>=2576,2600,IF(A10>=2526,2550,IF(A10>=2476,2500,IF(A10>=2426,2450,IF(A10>=2376,2400,IF(A10>=2326,2350,IF(A10>=2276,2300,IF(A10>=2226,2250,IF(A10>=2176,2200,IF(A10>=2126,2150,IF(A10>=2076,2100,IF(A10>=2026,2050,IF(A10>=1976,2000,IF(A10>=1926,1950,IF(A10>=1876,1900,IF(A10>=1826,1850,IF(A10>=1776,1800,IF(A10>=1726,1750,IF(A10>=1676,1700,IF(A10>=1626,1650,IF(A10>=1576,1600,IF(A10>=1526,1550,IF(A10>=1476,1500,IF(A10>=1426,1450,IF(A10>=1376,1400,IF(A10>=1326,1350,IF(A10>=1276,1300,IF(A10>=1226,1250,IF(A10>=1176,1200,IF(A10>=1126,1150,IF(A10>=1076,1100,IF(A10>=1026,1050,IF(A10>=976,1000,IF(A10>=926,950,IF(A10>=851,900,IF(A10>=776,800,IF(A10>=701,750,IF(A10>=626,650,IF(A10>=551,600,IF(A10>=476,500,IF(A10>=376,450,IF(A10>=176,300,150)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

[NBVC]

That's going to involve iterations to check combinations.. I can't immediately think of an efficient formula solution.. you would need VBA probably to do the iterating... which, unfortunately, is out of my scope here....

Hopefully someone can offer a VBA solution.. or an alternative that I haven't thought of for you.

[MrShorty]

As with a lot of these problems, I find it easier to work them out as algebra problems before trying to work them out as Excel problems. The thing that strikes me about this problem is that it seems quite similar to many "solve this system of equations" type problems. In this context, the problem might be stated as

Given that 150*m+500*n=950 and m+n=4, find m and n

Of course, your constraint, rather than a sum, is more like "m and n have to be integers >=0."

Off the top of my head, I don't see a ready way to put this into a single formula. My first thought would be to put this question to Solver. The spreadsheet should be simple (comma delimited):

mg/dose,doses of each
500,1
150,1

900,=sumproduct(a2:a3,b2:b3),=a5-b5

Run Solver, tell it to set C5 to a value of 0 by changing B2:B3 subject to the constraints that B2:B3>=0 and B2:B3 are integers. Some scenarios will have multiple solutions and Solver will converge on whichever it fells like at the time, but this should work given the information listed.

[leeayotte]

solver worked well.
Is there any way to automate it to run when data fields are changed

[Alf]

Perhaps something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim i As Integer

Set r = Range("F12:F17")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
  
'' Place solver run macro here

Application.EnableEvents = True
End Sub

This code should placed in the sheet where you have the solver setup. Right click on the sheet tab, click on "View code" and incert code.

Alf