Solver formula

[Solverer]

Hello Guys!
I have a question how to transform non-linear function like this =IF(AND(J7(600 000)>F7(fixed amount), D79(Current data)>L6(3%),D79<M6(4%)),M7(0.06),IF(AND(J7(600 000)>F7, D79(Current result)>M6(4%), D79<L6(5%)),L7(0.07) to the linear one, so I will be able to use the simplex method to solve the problem and not the evolutionary.
This is the maximising problem the data looks like this. The upper data is the ranges. so you will have 0.06 if the data less than 4% and more than 3% and the total amount is less than 600 000

	2.0000%	3.0000%	4.0000%	5.0000%	6.0000%	7.0000%	8.0000%	9.0000%	10.0000%
600000	0.04	0.05	0.06	0.07	10.00	0.07	0.06	0.05	0.04
2000000	0.04	0.05	0.06	0.07	12.00	0.07	0.06	0.05	0.04

 

[KRice]

I don't understand why Solver is needed. Are you trying to find which value in the body of the table corresponds to any combination of quantities that might appear in F7 (fixed) and D79 (current data)? If so, you could do that with a formula.

 

[Solverer]

I apologise for the long reply and for the misunderstanding with the formulas. Here is the simplified example.
There are 2 sellers who sold some amount of the products. In order to achieve the maximum bonus, sellers should sell from 50% to 60% of the total amount. The sellers can increase their bonuses by selling the additional product. These cells are variable. There are also constraints of the maximum amount that could be sold for each product for each seller.
The problem is to maximise the bonus by adjusting the additional product for each seller considering the constraints.

Is it possible somehow to adjust the formulas so it would be possible to use the simplex method because evolutionary does not perform well with many variables.

KPI optimization tool.xlsx
	Q	R	S	T	U	V	W	X	Y	Z
17
18
19		20.0000%	30.0000%	40.0000%	50.0000%	60.0000%	70.0000%	80.0000%	90.0000%	95.0000%
20		40.00	50.00	60.00	70.00	100.00	70.00	60.00	50.00	40.00
21
22
23
24			Seller 1					Seller 2
25		Total products	600000				Total products	200000
26		Sold product	200000				Sold product	60000
27		Percentge sold	33.3%				Percentge sold	30%
28
29
30
31
32
33		Additional product (Variable)	90000	48%			Additional product (Variable)	0	30%
34		Constraint	100000				Constraint	100000
35
36
37
38		Bonus 1 Seller	Bonus 2 seller
39		40	40
40
41
42		MAX function	80
43
44
45
Input

Cell Formulas
Range	Formula
S27,X27	S27	=S26/S25
T33	T33	=(S33+S26)/S25
Y33	Y33	=(X26+X33)/X25
R39	R39	=IF(T33<R20,R20,IF(AND(T33>R19,T33<S19),S20,IF(AND(T33>S19,T33<T19),T20,IF(AND(T33>T19,T33<U19),U20,IF(AND(T33>U19,T33<V19),V20,IF(AND(T33>V19,T33<W19),W20,IF(AND(T33>W19,T33<X19),X20,IF(AND(T33>X19,T33<Y19),Y20,IF(T33>Y19,Z20)))))))))
S39	S39	=IF(Y33<R20,R20,IF(AND(Y33>R19,Y33<S19),S20,IF(AND(Y33>S19,Y33<T19),T20,IF(AND(Y33>T19,Y33<U19),U20,IF(AND(Y33>U19,Y33<V19),V20,IF(AND(Y33>V19,Y33<W19),W20,IF(AND(Y33>W19,Y33<X19),X20,IF(AND(Y33>X19,Y33<Y19),Y20,IF(Y33>Y19,Z20)))))))))
S42	S42	=SUM(R39:S39)

 

[Solverer]

Corrected the mistake in the formula

KPI optimization tool.xlsx
	Q	R	S	T	U	V	W	X	Y	Z
17
18
19		20.0000%	30.0000%	40.0000%	50.0000%	60.0000%	70.0000%	80.0000%	90.0000%	95.0000%
20		40.00	50.00	60.00	70.00	100.00	70.00	60.00	50.00	40.00
21
22
23
24			Seller 1					Seller 2
25		Total products	600000				Total products	200000
26		Sold product	200000				Sold product	60000
27		Percentge sold	33.3%				Percentge sold	30%
28
29
30
31
32
33		Additional product (Variable)	90000	48%			Additional product (Variable)	2	30%
34		Constraint	100000				Constraint	100000
35
36
37
38		Bonus 1 Seller	Bonus 2 seller
39		70	60
40
41
42		MAX function	130
43
44
45
Input

Cell Formulas
Range	Formula
S27,X27	S27	=S26/S25
T33	T33	=(S33+S26)/S25
Y33	Y33	=(X26+X33)/X25
R39	R39	=IF(T33<R19,R20,IF(AND(T33>R19,T33<S19),S20,IF(AND(T33>S19,T33<T19),T20,IF(AND(T33>T19,T33<U19),U20,IF(AND(T33>U19,T33<V19),V20,IF(AND(T33>V19,T33<W19),W20,IF(AND(T33>W19,T33<X19),X20,IF(AND(T33>X19,T33<Y19),Y20,IF(T33>Y19,Z20)))))))))
S39	S39	=IF(Y33<R19,R20,IF(AND(Y33>R19,Y33<S19),S20,IF(AND(Y33>S19,Y33<T19),T20,IF(AND(Y33>T19,Y33<U19),U20,IF(AND(Y33>U19,Y33<V19),V20,IF(AND(Y33>V19,Y33<W19),W20,IF(AND(Y33>W19,Y33<X19),X20,IF(AND(Y33>X19,Y33<Y19),Y20,IF(Y33>Y19,Z20)))))))))
S42	S42	=SUM(R39:S39)

 

[KRice]

Thanks for the clarification on this. I was going to ask about the R20 --> R19 issue in the formulas, but I see that's been addressed.

Does "total products" represent the amount of product initially allocated to a seller?

What was unclear initially, and a bit clearer now (but I want to confirm)...are you saying that the constraint on the problem is that the total additional product (i.e., the sum of what is allocated to Seller 1 and to Seller 2) cannot exceed the "Constraint". So for example, if you input 100,000 as a max constraint, then add'l product seller 1 + add'l product seller 2 <= 100,000?

 

[KRice]

I haven't encountered any issues running this in SOLVER, even with a nonlinear method. Here is a reworked version of the sheet you posted. I changed the formula that returns the bonus amount. One thing I noticed is that your original formula does not handle equalities, which is why FALSE appears in S40 (because the fraction to compare is S35 (=0.3) and 0.3 is not greater than or less than any of the conditions in the formula). The reworked formula assumes that you want to find the largest value in R19:Z19 that is less than U35 (for seller 1) (or V35 for seller 2), and since that threshold was passed, the bonus amount awarded comes from the next higher column. This may not be correct, but it is nearly consistent with your original formula, with the exception that it assigns the equality condition to the next higher bracket. If you want the equality condition to assign the bonus amount from the lower bracket, the formula can easily be adjusted. I've rearranged some content and shown a SOLVER block with a sample image of the SOLVER window showing what was specified to obtain the outputs in the orange cells and the maximized total bonus amount of 160.

MrExcelBook20200430.xlsx
	Q	R	S	T	U	V	W	X	Y	Z
18
19	Fraction of product sold	0.2	0.3	0.4	0.5	0.6	0.7	0.8	0.9	0.95
20	Bonus Amount	40	50	60	70	100	70	60	50	40
21
22					SOLVER block
23
24		Seller 1	Seller 2		Seller 1	Seller 2
25	Total products	600,000	200,000		600,000	200,000
26	Sold product	200,000	60,000		200,000	60,000
27	Fraction sold	0.333333	0.3		0.333333	0.3
28
29
30	Constraint						100,000
31
32							sum
33	Incentive Add'l product	90,000	0		0	52,429	52,429
34	Seller Max Constraint	100,000	100,000
35	Fraction sold wrt total	0.483333	0.3		0.333333	0.562144
36
37
38
39	Bonus	70	FALSE
40		70	50		60	100
41
42	MAX function	70
43		120			160
44
Input

Cell Formulas
Range	Formula
U27:V27,R27:S27	R27	=R26/R25
W33	W33	=SUM(U33:V33)
R35,U35	R35	=(R33+R26)/R25
S35,V35	S35	=(S26+S33)/S25
R39	R39	=IF(R35<R19,R20,IF(AND(R35>R19,R35<S19),S20,IF(AND(R35>S19,R35<T19),T20,IF(AND(R35>T19,R35<U19),U20,IF(AND(R35>U19,R35<V19),V20,IF(AND(R35>V19,R35<W19),W20,IF(AND(R35>W19,R35<X19),X20,IF(AND(R35>X19,R35<Y19),Y20,IF(R35>Y19,Z20)))))))))
S39	S39	=IF(S35<R19,R20,IF(AND(S35>R19,S35<S19),S20,IF(AND(S35>S19,S35<T19),T20,IF(AND(S35>T19,S35<U19),U20,IF(AND(S35>U19,S35<V19),V20,IF(AND(S35>V19,S35<W19),W20,IF(AND(S35>W19,S35<X19),X20,IF(AND(S35>X19,S35<Y19),Y20,IF(S35>Y19,Z20)))))))))
U40:V40,R40:S40	R40	=INDEX($R$20:$Z$20,,AGGREGATE(14,6,(R$35>$R$19:$Z$19)*(COLUMN($R$20:$Z$20)-COLUMN($R$20)+1),1)+1)
U43,R42:R43	R42	=SUM(R39:S39)

 

[Solverer]

Thank you for you valuable reply!
Regarding the constraints they are individual for the each variable cell, however, in this case it could be also the sum.

The formulas are really cool and thank you for the help with inequities! I have not thought about finding these values in such a way.
The issue is that with non-linear problems the solver finds the local optimum and then its stops. Even in such a simple example it was not able to find the maximum value of 170. Is it possible to adjust the formulas and make the linear problem?

 

[KRice]

I see your point. I don't think there is a good way to reformulate this, but it would make sense to explore various initial guesses for the Incentive amounts. For example, if I enter 55,000 for the incentive product amounts for both sellers in R32:S32 (I deleted the original block), then I get a sum of 110,000 which violates the constraint. Solver then works to satisfy the constraint and finds an answer of 49,500 incentive amount for both sellers, with bonuses of 70 and 100, for a total of 170...using the nonlinear solver. I suspect you may have to try multiple guesses to evaluate the behavior before having confidence that a global maximum was found. I'll look at this some more to see if there might be other options.

I wanted to confirm then, if a seller has fractions sold of 0.29, 0.30, and 0.31, what are the correct bonus amounts: is it 50, 50, and 60, respectively? I'd like to make sure the bonus tiers table is being interpreted correctly.

MrExcelBook20200430.xlsx
	Q	R	S	T	U	V	W	X	Y	Z
19	Fraction of product sold	0.2	0.3	0.4	0.5	0.6	0.7	0.8	0.9	0.95
20	Bonus Amount	40	50	60	70	100	70	60	50	40
21
22		SOLVER block
23
24		Seller 1	Seller 2
25	Total products	600,000	200,000
26	Sold product	200,000	60,000
27	Fraction sold	0.33333	0.3
28
29	Constraint			100,000
30
31				sum
32	Incentive Add'l product	49,500	49,500	99,000
33	Seller Max Constraint
34	Fraction sold wrt total	0.41583	0.5475
35				sum
36	Bonus	70	100	170
Input (2)

Cell Formulas
Range	Formula
R27:S27	R27	=R26/R25
T32,T36	T32	=SUM(R32:S32)
R34:S34	R34	=(R26+R32)/R25
R36:S36	R36	=INDEX($R$20:$Z$20,,AGGREGATE(14,6,(R$34>$R$19:$Z$19)*(COLUMN($R$20:$Z$20)-COLUMN($R$20)+1),1)+1)

 

[Solverer]

Thank you for help with case! Regarding the guessing part it could be quite problematic since I will have to add more variables.
Regarding the bonus part if it is 0.29 the bonus is 50 if 0.3, 0.31 the bonus is 60

 

[KRice]

Thanks for the clarification. I agree with your point about guessing being problematic; however, that tends to be an issue with many optimization search algorithms, as the initial guess and first few iterations can begin to focus on parts of the surface containing local extrema, but not necessarily the global extremum. As for the question about assigning bonus values around a fraction of product sold = 0.3, it sounds like the Bonus formulas need to be revised....the ">" becomes a ">=" in the AGGREGATE functions. Then when the fraction sold equals or exceeds a tier threshold, the bonus amount listed for the next tier applies....so f<0.2 --> bonus of 40, 0.2<=f<0.3 --> bonus of 50, 0.3<=f<0.4 --> bonus of 60, and so on. This will resolve the equality issue I mentioned earlier and clears up some confusion I had about it.