If , Round and Max with criterias.

[Velven]

Hello Guys !

Is there any formula to do automate this calculation ?

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10% Discount[/TD]
[TD]3[/TD]
[TD]( Insert Formula ) [/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20% Discount[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]15% Discount[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]VIP 7%[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]143[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Weekend 25%[/TD]
[TD]25[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Weekend 25%[/TD]
[TD]25[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Weekend 25%[/TD]
[TD]25[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]15% Discount[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]20% Discount[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]



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
Discount	Amt	Formula	Discount	Rate	Cap
10% Discount	10% Discount
20% Discount	20% Discount
15% Discount	15% Discount
VIP 7%	VIP 7%
Weekend 25%	Weekend 25%
Weekend 25%
Weekend 25%
15% Discount
20% Discount

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10%[/TD]
[TD="align: right"]9999999[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]

[TD="align: right"]20%[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]333[/TD]
[TD="align: right"][/TD]

[TD="align: right"]15%[/TD]
[TD="align: right"]9999999[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]143[/TD]
[TD="align: right"][/TD]

[TD="align: right"]7%[/TD]
[TD="align: right"]9999999[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"]25%[/TD]
[TD="align: right"]9999999[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=ROUND(MIN(B2/VLOOKUP(A2,$E$2:$G$6,2,0),VLOOKUP(A2,$E$2:$G$6,3,0)),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]10% Discount[/td][td]

3​

[/td][td]

30​

[/td][td]

30​

[/td][td][/td][td]10% Discount[/td][td]

10%​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]20% Discount[/td][td]

10​

[/td][td]

30​

[/td][td]

30​

[/td][td][/td][td]15% Discount[/td][td]

15%​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]15% Discount[/td][td]

50​

[/td][td]

333.3333333​

[/td][td]

333​

[/td][td][/td][td]VIP 7%[/td][td]

7%​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]VIP 7%[/td][td]

10​

[/td][td]

142.8571429​

[/td][td]

143​

[/td][td][/td][td]Weekend 25%[/td][td]

25%​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Weekend 25%[/td][td]

25​

[/td][td]

100​

[/td][td]

100​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Weekend 25%[/td][td]

25​

[/td][td]

100​

[/td][td]

100​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Weekend 25%[/td][td]

25​

[/td][td]

100​

[/td][td]

100​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]15% Discount[/td][td]

10​

[/td][td]

66.66666667​

[/td][td]

67​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]20% Discount[/td][td]

10​

[/td][td]

30​

[/td][td]

30​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet75[/td][/tr][/table]

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)))