Using Excel Formula instead of Solver addins

[ExcelNewbie2020]

having multiple projects with limited budget. Using excel formula, need to maximize the number of quantity without exceeding the set budget cost.. This scenario could be solved by the solver, but the solver needs to recalculate everytime the data change.

Excel_Free_Workbook_Solver_Basics_XelPlus.xlsx
	A	B	C	D	E	F	G	H	I	J
1	RAW DATA							EXPECTED RESULT
2	Project name	Total Costs	Qty					Project name	Total Costs	Qty
3	Project A	6400	35					Project A	6400	35
4	Project B	7900	20					Project D	6500	40
5	Project C	6000	13					Project F	3200	30
6	Project D	6500	40					Project G	8700	32
7	Project E	3400	25					Project I	9000	36
8	Project F	3200	30					Project E	3400	25
9	Project G	8700	32						37200	198
10	Project H	9600	15
11	Project I	9000	36
12		60700
13
14	Constraints:
15	budget cost	40000
16
17
18
19
Sheet1

Cell Formulas
Range	Formula
I9:J9	I9	=SUM(I3:I8)

 

[Anonymous1378]

Try this? It won't work beyond a certain number of projects...

Book1
	A	B	C	D	E	F	G	H	I	J
1	RAW DATA							EXPECTED RESULT
2	Project name	Total Costs	Qty					Project name	Total Costs	Qty
3	Project A	6400	35					Project A	6400	35
4	Project B	7900	20					Project D	6500	40
5	Project C	6000	13					Project E	3400	25
6	Project D	6500	40					Project F	3200	30
7	Project E	3400	25					Project G	8700	32
8	Project F	3200	30					Project I	9000	36
9	Project G	8700	32						37200	198
10	Project H	9600	15
11	Project I	9000	36
12		60700
13
14	Constraints:
15	budget cost	40000
Sheet2

Cell Formulas
Range	Formula
H3:J8	H3	=LET( rng,A3:C11, con,B15, _a,INDEX(rng,,2), _b,--MID(BASE(SEQUENCE(2^COUNTA(_a)-1),2,COUNTA(_a)),SEQUENCE(,COUNTA(_a)),1), _c,MMULT(_b,_a), _d,MMULT(_b,INDEX(rng,,3)), FILTER(rng,TRANSPOSE(FILTER(_b,(_d=MAX(FILTER(_d,_c<=con)))*(_c<=con)))))
I9:J9	I9	=SUM(I3:I8)
Dynamic array formulas.

 

[ExcelNewbie2020]

Try this? It won't work beyond a certain number of projects...

Book1
	A	B	C	D	E	F	G	H	I	J
1	RAW DATA							EXPECTED RESULT
2	Project name	Total Costs	Qty					Project name	Total Costs	Qty
3	Project A	6400	35					Project A	6400	35
4	Project B	7900	20					Project D	6500	40
5	Project C	6000	13					Project E	3400	25
6	Project D	6500	40					Project F	3200	30
7	Project E	3400	25					Project G	8700	32
8	Project F	3200	30					Project I	9000	36
9	Project G	8700	32						37200	198
10	Project H	9600	15
11	Project I	9000	36
12		60700
13
14	Constraints:
15	budget cost	40000
Sheet2

Cell Formulas
Range	Formula
H3:J8	H3	=LET( rng,A3:C11, con,B15, _a,INDEX(rng,,2), _b,--MID(BASE(SEQUENCE(2^COUNTA(_a)-1),2,COUNTA(_a)),SEQUENCE(,COUNTA(_a)),1), _c,MMULT(_b,_a), _d,MMULT(_b,INDEX(rng,,3)), FILTER(rng,TRANSPOSE(FILTER(_b,(_d=MAX(FILTER(_d,_c<=con)))*(_c<=con)))))
I9:J9	I9	=SUM(I3:I8)
Dynamic array formulas.

it workx man.. thank you so much

 

[ExcelNewbie2020]

Try this? It won't work beyond a certain number of projects...

Book1
	A	B	C	D	E	F	G	H	I	J
1	RAW DATA							EXPECTED RESULT
2	Project name	Total Costs	Qty					Project name	Total Costs	Qty
3	Project A	6400	35					Project A	6400	35
4	Project B	7900	20					Project D	6500	40
5	Project C	6000	13					Project E	3400	25
6	Project D	6500	40					Project F	3200	30
7	Project E	3400	25					Project G	8700	32
8	Project F	3200	30					Project I	9000	36
9	Project G	8700	32						37200	198
10	Project H	9600	15
11	Project I	9000	36
12		60700
13
14	Constraints:
15	budget cost	40000
Sheet2

Cell Formulas
Range	Formula
H3:J8	H3	=LET( rng,A3:C11, con,B15, _a,INDEX(rng,,2), _b,--MID(BASE(SEQUENCE(2^COUNTA(_a)-1),2,COUNTA(_a)),SEQUENCE(,COUNTA(_a)),1), _c,MMULT(_b,_a), _d,MMULT(_b,INDEX(rng,,3)), FILTER(rng,TRANSPOSE(FILTER(_b,(_d=MAX(FILTER(_d,_c<=con)))*(_c<=con)))))
I9:J9	I9	=SUM(I3:I8)
Dynamic array formulas.

sir, the formula initially works, but when i run and play it with my actual file it sometimes returns to error. I noticed the error occur if there is a tie qty near the bottom cost..

Excel_Free_Workbook_Solver_Basics_XelPlus.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	RAW DATA							EXPECTED RESULT (NEW)
2	Project name	Total Costs	Qty					Project name	Total Costs	Qty
3	Project D	8000	46					Project D	8000	46
4	Project C	9600	45					Project I	7000	43
5	Project I	7000	43					Project B	6000	42
6	Project A	6000	42					Project G	5000	40
7	Project B	6000	42					Project H	4500	39
8	Project G	5000	40					Project F	4500	39
9	Project H	4500	39					Project E	4500	39
10	Project F	4500	39						39500	288
11	Project E	4500	39
12
13
14
15	budget cost	40000
16
17
18
19
20								#VALUE!
21
22
23
24
25
26
Sheet1

Cell Formulas
Range	Formula
I10:J10	I10	=SUM(I3:I9)
H20	H20	=LET( rng,A3:C11, con,B15, _a,INDEX(rng,,2), _b,--MID(BASE(SEQUENCE(2^COUNTA(_a)-1),2,COUNTA(_a)),SEQUENCE(,COUNTA(_a)),1), _c,MMULT(_b,_a), _d,MMULT(_b,INDEX(rng,,3)), FILTER(rng,TRANSPOSE(FILTER(_b,(_d=MAX(FILTER(_d,_c<=con)))*(_c<=con)))))

 

[Anonymous1378]

It's because there's a draw in the max quantity within your budget. What do you want to happen when there is a draw?

 

[ExcelNewbie2020]

thank you, just like on the expected result. if there is a draw, check for the project cost then optimize the remaining cost

 

[Anonymous1378]

The cost and quantity in your example is the same though; so it should just pick either one?
D-I-B/A-G-H-F-E

 

[Anonymous1378]

Anyway try:

=LET(
rng,A3:C11,
con,B15,
_a,INDEX(rng,,2),
_b,--MID(BASE(SEQUENCE(2^COUNTA(_a)-1),2,COUNTA(_a)),SEQUENCE(,COUNTA(_a)),1),
_c,MMULT(_b,_a),
_d,SORTBY(MMULT(_b,INDEX(rng,,3)),_c),
FILTER(rng,TRANSPOSE(INDEX(FILTER(SORTBY(_b,_c),(_d=MAX(FILTER(_d,SORT(_c)<=con)))*(SORT(_c)<=con)),1,))))

 

[ExcelNewbie2020]

awesomeeeee.. thanks sir..make my life easier

 

[ExcelNewbie2020]

The cost and quantity in your example is the same though; so it should just pick either one?
D-I-B/A-G-H-F-E

if its the case (same qty), the lowest cost should get pick