If , Round and Max with criterias.

[Velven]

Hello Guys !

Is there any formula to do automate this calculation ?

	A	B	C	D
1	10% Discount	3	( Insert Formula )	30
2	20% Discount	10		30
3	15% Discount	50		333
4	VIP 7%	10		143
5	Weekend 25%	25		100
6	Weekend 25%	25		100
7	Weekend 25%	25		100
8	15% Discount	10		67
9	20% Discount	10		30

<tbody>
</tbody>

My Expected Answer is in D.
Why is 20% listed as 30 because maximum claimable amount is 30 only. So I expect a Max formula here.

Formula should be With Round figures.
10% Discount = 10%
20% Discount = 20% with max capped of RM 30.
15% Discount = 15%
Weekend 25% = 25%
VIP 7% = 7%

I've been using And the list goes on and on. Any simpler way of doing it? And I want to add a max formula too.
Round(if(A1="10% Discount",B1/10%)+If(A1="20% Discount",B1/20%)

 

[Eric W]

You could build a table like this:

	A	B	C	D	E	F	G
1	Discount	Amt	Formula		Discount	Rate	Cap
2	10% Discount	3	30		10% Discount	10%	9999999
3	20% Discount	10	30		20% Discount	20%	30
4	15% Discount	50	333		15% Discount	15%	9999999
5	VIP 7%	10	143		VIP 7%	7%	9999999
6	Weekend 25%	25	100		Weekend 25%	25%	9999999
7	Weekend 25%	25	100
8	Weekend 25%	25	100
9	15% Discount	10	67
10	20% Discount	10	30

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula C2 =ROUND(MIN(B2/VLOOKUP(A2,$E$2:$G$6,2,0),VLOOKUP(A2,$E$2:$G$6,3,0)),0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​	G​
1​	10% Discount	3​	30​	30​		10% Discount	10%​
2​	20% Discount	10​	30​	30​		15% Discount	15%​
3​	15% Discount	50​	333.3333333​	333​		VIP 7%	7%​
4​	VIP 7%	10​	142.8571429​	143​		Weekend 25%	25%​
5​	Weekend 25%	25​	100​	100​
6​	Weekend 25%	25​	100​	100​
7​	Weekend 25%	25​	100​	100​
8​	15% Discount	10​	66.66666667​	67​
9​	20% Discount	10​	30​	30​

Sheet: Sheet75

Formula in cell C1:

=IF(A1="20% Discount",IF(B1/0.2>30,30,B1/0.2),B1/INDEX($G$1:$G$4,MATCH(A1,$F$1:$F$4,0)))