Choosing multiple cells based on achieving a goal value?

[NamssoB]

I'm looking for some type of "Goal Seek", but for selecting multiple cells that when added (multiplied, etc.) come closest to a specific amount.

Scenario: I have a list of values in column B. I want to know which values, when combined in some way (generally add), come closest to a specific goal value (B16). It's sort of like a Goal Seek, but instead of changing one cell to affect another cell, I am "selecting" multiple cells to try and match another cell. Bottom line, "Which deals do I need to close this month to get to $50,000?". Going over is allowed, but which deals put me over the LEAST?

For Example: Right now, I have to manually select or put an X in a cell in column C to manually optimize which deals come closest to my $50k goal. This is a short example, usually I have 20-100 rows of data and need to pick the "best" 5-10 of them to come closest to the goal amount, "closest" meaning must equal or exceed the goal amount, as close as possible.

 

[KRice]

Have a look at a recent post of mine where I describe an approach for combinatorial sums. The workbook I link to in that post can be adapted to your situation. In terms of more preferred options, are you interested in how many deals are involved in meeting the total goal amount, or only in meeting the total goal amount regardless of the number of deals?

You're an Expert? Check this out! (Weighted Distribution in Groups)

Hello Everyone, Sorry for the clickbait. I have combed the internet to find a solution for this but I was unable to come up with something that addressing my requirements. They are as follows. 1. There is a list of users which can be even or odd in number (preferable in a separate sheet since...

www.mrexcel.com

I'll work up a variation of the posted workbook to give you a better idea what the outputs look like. The current version looks at 6000 rows of results and picks from them those that consist of "x" number of values ("deals" in your case) that also come close to a target sum. But if you don't need that functionality, that part of the post-processing can be eliminated.

 

[NamssoB]

Have a look at a recent post of mine where I describe an approach for combinatorial sums. The workbook I link to in that post can be adapted to your situation. In terms of more preferred options, are you interested in how many deals are involved in meeting the total goal amount, or only in meeting the total goal amount regardless of the number of deals?

You're an Expert? Check this out! (Weighted Distribution in Groups)

Hello Everyone, Sorry for the clickbait. I have combed the internet to find a solution for this but I was unable to come up with something that addressing my requirements. They are as follows. 1. There is a list of users which can be even or odd in number (preferable in a separate sheet since...

www.mrexcel.com

I'll work up a variation of the posted workbook to give you a better idea what the outputs look like. The current version looks at 6000 rows of results and picks from them those that consist of "x" number of values ("deals" in your case) that also come close to a target sum. But if you don't need that functionality, that part of the post-processing can be eliminated.

Awesome, I'll check this out. And to answer your question, I'm looking for the latter, "meeting the total goal amount regardless of the number of deals".

Assume there is a reward for achieving the total goal amount, but the reward is reduced for each $1 over the goal...so in a perfect world, the exact goal amount is achieved.

 

[KRice]

Review my posts in the link and then if you are interested in learning more about the code I adapted, there are secondary links within my other posts that go back to the source of the code and commentary from the author (Tony Dallimore). I've adjusted a parameter in his code to return 6000 results in the [Result] sheet, and those results are simply targeting some value. I haven't dug into the code enough to understand how exhaustive the search is to populate these 6000 rows. The code incorporates some methods for reducing the amount of searching, and for swapping in better results for the worst that are in the table. I'm discovering that there are some duplicates in the results table, which is unfortunate because eliminating them would open up space to receive results from other combinations, and in some situations that can improve the efficiency of the program (e.g., if one wishes to find optimal solutions consisting of k items from the original group of n values). In any case, some post-processing is necessary to extract combinations of interest to you.

Here is brief overview to obtain a solution.

1. Set up your initial data table in F3:H3 and down. The "Consider?" column (H3 and down) is used to flag those deals that you want to consider. A standing KeyCodes column E5 and down is used to assign unique, easy to reference, two-character codes to each item in your data table, and the VBA code utilizes these KeyCodes and reports results back in terms of the KeyCodes.
2. Formulas compute the counts and sums of all deals and selected deals to consider (F2:I3). A formula also creates a consolidated table of deals to consider (I6:K6 and down). See the yellow note in J4:K4...the consolidated list in J6:K6 and down is copied and then Paste>Paste Special>Values in cell A2. This manual step is necessary because the VBA code will rewrite this information in A2:B2 and down with a sorted table.
3. Enter the target sum in C2.
4. Check cells N1:N2 to confirm that the range of results to be considered is acceptable (you can change this after the code runs, if desired)...adjust those formula to change the floor and ceiling.
5. Run the code: Developer>Macros then choose Control3 and run. On my system, the example here takes about 40 seconds. The [Result] sheet should now be updated with up to 6000 rows of results that are somewhere around the target value: many will be above or below the target.
6. On the [Source] sheet, the post-processing is done in columns N:Q. Using the limits established in N1:N2, a formula extracts from [Result] those KeyCode combinations and associated sums that satisfy the floor/ceiling limits, and then those results are sorted from best to worst. Other formulas show the number of deals in each KeyCode combination and the row index in the [Result] table where that item is found (the row index begins a "1" on row 2).
7. Examine the results in N8:Q8 and down and enter the Row Index of interest in cell N3 and column L will update with results for that particular selection, including cross-referencing the KeyCodes to the original Customer name.

Here is a mini-sheet of the main [Source] worksheet:

MrExcel_20220728_CombinatoricsSum_NamssoB_v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Key	Value	Target									AG+AH+AI+AA+AB+AL+AF	<-- KeyCodes "optimum" group	55000	<-- min sum req'd
2	AJ	55	55000			Cust Count	Sum All Deals	156067	<-- Sum Amts for deals considered					56000	<-- max sum req'd
3	AG	1877				13	156067	13	<-- Number of deals considered			55092	<-- Sum Amts	2216	<-- Enter Row Index of interest
4	AH	1877				Maintain customers/amts		If yes, mark "X"		Paste values to A:B		7	<-- Count
5	AI	2088			KeyCodes	Customer	Amt	Consider?	Customer	KeyCode	Amt	Results		130	<-- number of results
6	AA	5500			AA	A	5500	x	A	AA	5500	A
7	AB	6750			AB	B	6750	x	B	AB	6750	B		Sum	KeyCode Combination	Number of Deals	Row Index
8	AL	17200			AC	C	22500	x	C	AC	22500			55092	AG+AH+AI+AA+AB+AL+AF	7	2216
9	AM	17200			AD	D	22500	x	D	AD	22500			55092	AG+AH+AI+AA+AB+AM+AF	7	2242
10	AL	17200			AE	E	19360	x	E	AE	19360			55147	AJ+AG+AH+AI+AA+AB+AL+AF	8	4271
11	AM	17200			AF	F	19800	x	F	AF	19800	F		55147	AJ+AG+AH+AI+AA+AB+AM+AF	8	4297
12	AE	19360			AG	G	1877	x	G	AG	1877	G		55164	AG+AH+AA+AB+AE+AF	6	2787
13	AK	19360			AH	H	1877	x	H	AH	1877	H		55164	AG+AH+AA+AB+AK+AF	6	2791
14	AF	19800			AI	I	2088	x	I	AI	2088	I		55219	AJ+AG+AH+AA+AB+AE+AF	7	4842
15	AC	22500			AJ	J	55	x	J	AJ	55			55219	AJ+AG+AH+AA+AB+AK+AF	7	4846
16	AD	22500			AK	K	19360	x	K	AK	19360			55354	AG+AH+AL+AM+AL	5	3061
17					AL	L	17200	x	L	AL	17200	L		55354	AG+AH+AL+AM+AM	5	3062
18					AM	M	17200	x	M	AM	17200			55354	AG+AH+AL+AL+AM	5	3069
19					AN									55354	AG+AH+AM+AL+AM	5	3098
Source

Cell Formulas
Range	Formula
L1	L1	=INDEX(INDIRECT("Result!$c$2:$c$6001"),$N$3)
H2	H2	=SUMIF(Table13[Consider?],"x",Table13[Amt])
N1	N1	=C2
N2	N2	=N1+1000
F3	F3	=COUNTA(Table13[Customer])
G3	G3	=SUM(Table13[Amt])
H3	H3	=COUNTIF(Table13[Consider?],"x")
L3	L3	=SUMPRODUCT(INDEX($I$6#,,3),--(L6#<>""))
L4	L4	=SUMPRODUCT(--(L6#<>""))
N5	N5	=COUNT(INDEX(N8#,,1))
I6:K18	I6	=FILTER(CHOOSE({1,2,3},$F$6:$F$44,$E$6:$E$44,$G$6:$G$44),$H$6:$H$44="x")
L6:L18	L6	=IF(INDEX(I6#,,1)="","",IF(ISNUMBER(SEARCH(INDEX(I6#,,2),$L$1)),INDEX(I6#,,1),""))
N8:O137	N8	=SORT(FILTER(UNIQUE(FILTER(INDIRECT("Result!$A$2:$C$6001"),(INDIRECT("Result!$A$2:$A$6001")>=$N$1)*(INDIRECT("Result!$A$2:$A$6001")<=$N$2))),{1,0,1}),1)
P8:P137	P8	=LEN(INDEX(N8#,,2))-LEN(SUBSTITUTE(INDEX(N8#,,2),"+",""))+1
Q8:Q137	Q8	=MATCH(INDEX(N8#,,2),INDIRECT("Result!$C$2:$C$6001"),0)
Dynamic array formulas.

The working version of this file with the VBA code is here:

MrExcel_20220728_CombinatoricsSum_NamssoB_v2.xlsm

Shared with Dropbox

www.dropbox.com