Excel formula to modify the percentages (say discount) so that final sum after the discount is within the budget

[Himaja]

After applying discounts (x%,2x%,3x%) to premium, the final discounted premium price is 1.1 million. But maximum final discounted premium price that I can give is 1 Million. Now, how to adjust the discounts (x%,2x%,3x%), so that my final discounted prices is exactly 1M


Note: I have to use only these 3 discounts x%, 2x%, 3x%

 

[Dave Patton]

Your information is not very clear.

=min(1000000,your formula)

 

[Toadstool]

Hi Himaja,

Here is one way:

Himaja.xlsx
	C	D	E	F	G	H	I	J
1	Premium	x		x%	2x%	3x%		Max
2	$7,000,000	10.00%		10.00%	20.00%	30.00%		$1,000,000
3
4	Initial Discount
5	$4,200,000
6
7	Initial Discounted Premium
8	$2,800,000
9
10	Final Discounted Premium
11	$1,000,000	14.29%		14.29%	28.57%	42.86%
Sheet1

Cell Formulas
Range	Formula
F2,F11	F2	=D2
G2,G11	G2	=2*F2
H2,H11	H2	=3*F2
C5	C5	=(C2*(SUM(F2:H2)))
C8	C8	=C2-C5
C11	C11	=IF(C8>J2,C2-(C2*(SUM(F11:H11))),C8)
D11	D11	=IF(C8>J2,((C2-J2)/C2)/6,D2)

 

[Himaja]

Hi Himaja,

Here is one way:

Himaja.xlsx
	C	D	E	F	G	H	I	J
1	Premium	x		x%	2x%	3x%		Max
2	$7,000,000	10.00%		10.00%	20.00%	30.00%		$1,000,000
3
4	Initial Discount
5	$4,200,000
6
7	Initial Discounted Premium
8	$2,800,000
9
10	Final Discounted Premium
11	$1,000,000	14.29%		14.29%	28.57%	42.86%
Sheet1

Cell Formulas
Range	Formula
F2,F11	F2	=D2
G2,G11	G2	=2*F2
H2,H11	H2	=3*F2
C5	C5	=(C2*(SUM(F2:H2)))
C8	C8	=C2-C5
C11	C11	=IF(C8>J2,C2-(C2*(SUM(F11:H11))),C8)
D11	D11	=IF(C8>J2,((C2-J2)/C2)/6,D2)

Hi Toad,

Thank you for your reply. But my sheet looks like this where I have multiple rows.

 

[Dave Patton]

N.B. You can post an extract of your information with XL2BB. (see add-on highlighted at the right of this message).
If the extract is too large, you could prepare a concise example complete with expected result.

 

[Himaja]

I cannot run that in my company laptop because of security issues

 

[Dave Patton]

O.K.
I did also state
If the extract is too large, you could prepare a concise example complete with expected result.
End

 

[Himaja]

I got the result. I used Data Analysis - What IF Analysis to accomplish that. Thanks a lot for your time