Thread: calculating numbers needed to achieve best result

I need to find a way to quickly calculate which numbers I need to put into column F to achieve the highest number possible in H11. Column F can contain any numbers between 300 and 420 but column G has to add up to a specified number that will vary from day to day. The numbers in column A and column B will also vary from day to day.


Any help would be greatly appreciated, even if it's just a suggested jumping off point.
Thank You!!

I'd google linear programming and excel.

Welcome to the forum.

Use Solver. See http://office.microsoft.com/en-us/ex...001124595.aspx.

E.g., maximize H11 by changing F1:F10, subject to the constraints

F1:F10 >= 320
F1:F10 <= 400
G11 = 800000

       ---A---- ---B--- ----C---- -D- -E-- --F-- ----G---- ----H-----
   1     2,010      51     8,160  50% 5.00  320    16,320    (30,750)
   2    39,159   1,065   272,640  80% 0.75  320   340,800   (175,111)
   3     3,334      84    30,240  90% 0.25  400    33,600     (6,727)
   4    13,272     366    14,640  10% 2.00  400   146,400     (2,736)
   5    12,738     129     7,740  15% 1.00  400    51,600      4,998 
   6    14,336     115    36,331  87% 0.57  363    41,760    (12,537)
   7     2,183      18     1,728  24% 0.65  400     7,200        296 
   8     5,902      39    12,131  97% 0.76  320    12,480     (4,734)
   9    29,353     269     1,775   2% 0.80  400   107,600     22,062 
  10    13,034     132    36,749  87% 3.00  320    42,240    (71,144)
  11                                              800,000   (276,383)