1

I am trying to pick the best scenario from data in two columns: Column 1 contains values for 20 rows (4, 6, 8, 10.5, 13, etc). Column 2 contains values for the same 20 rows (1100, 950, 800, 750, etc).

I need to look for the 6 lowest possible average value in column 1 while not exceeding a specific value in column 2. e.g. 5000. The goal is to have the lowest value for column 1 while getting as close to 5000 in column 2.

Example: Row1 4, 1100, Row3 8, 800, etc. This would be avg 6 total of 1900. (I just did 2 rows, but I need the best of 6.) I tried to post a screenshot, but I don't have enough reputation points. :(

Any help would be greatly greatly appreciated!

Dean
  • 11
  • Did you get anywhere? You question will probably fair better if you say something about what you're tried. – aparente001 Sep 19 '15 at 05:07
  • (1) Please use correct terminology.  In Excel, the columns are named A, B, etc.  There is no such thing as “Column 1”.  (2) Actually, it's better not to use screenshots.  Just type in representations of your data, as was done here and here.  (3) And it would be much better if you would post an example that illustrates your problem, rather than just fitting the description.  For example, it seems "obvious" that the solution is to pick the smallest six numbers from Column A.  … (Cont’d) – Scott - Слава Україні Sep 19 '15 at 07:34
  • (Cont’d) …  But I guess the Column B constraint makes it more complicated.  Please post a *clear* example where the solution is not the six lowest numbers from Column A.  (It goes without saying that such an example must have at least seven rows.)  (4) Do you want the six lowest possible average value in column A while not exceeding a total of 5000 in column B, or do you want to get as close as possible to 5000?  What does “getting as close to 5000 in column B” mean?  (5) Just off the top of my head, I believe that 1100+800 is 1900. – Scott - Слава Україні Sep 19 '15 at 07:34
  • Thanks for your feedback and your attempt to clarify my request. You hit the nail pretty much on the head as described in your (4) above. I would like the six lowest possible average values in column A while not exceeding a total value of 5000 in column B, yet getting as close to the value of 5000 as possible. – Dean Sep 21 '15 at 03:56
  • You have two conflicting objectives: the six lowest values that don't exceed 5000 are not necessarily the same as the six lowest values that come the closest to 5000 without exceeding it. Is the highest priority the lowest six values, or closeness to 5000? – fixer1234 Sep 21 '15 at 04:09

1 Answers1

0

I would use the Power Query Add-In for this. I had a go at this particular challenge and got it working. You can download and use my demo file from my OneDrive:

http://1drv.ms/1AzPAZp

It's the file: Power Query demo - Best combinations of values from two columns.

As described on the ReadMe sheet, I didn't have to write many functions - it was mostly built by clicking around the UI.

My design is to join every row combination together 5 times to get every possible combination of 6 rows. Then I calculate the Average and Sum values, then Filter and Sort on them to get the best combination.

Mike Honey
  • 2,562