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

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,632