Multiple If Conditions

[RAMU]

Dear Friends,

Working in a workbook where there are multiple if conditions:


Sheet1

	A	B	C	D	E	F	G	H	I	J
MAA LAXMI TRADER	14-08-16	D	A-122		0
BISWAS STORES	13-09-16		B-20		-3
MAA LAXMI TRADER	12-09-16	D	A-215		91
BISWAS STORES	12-12-16		B-165		181
RAJA ENTERPRISE	26-07-16	D	A-05		178
BISWAS STORES	22-12-16		B-183		76

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 176px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 108px"><col style="WIDTH: 95px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] , align: center"]PARTY NAME[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] , align: center"]DATE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] , align: center"]TYPE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] , align: center"]DOC[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] , align: center"]ADDRESS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] , align: center"]GROSS VALUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] , align: center"]ADV[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] , align: center"]AMOUNT[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] , align: center"]Days [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] , align: center"]Rebate[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]20500[/TD]
[TD="align: right"]2200[/TD]
[TD="align: right"]22700[/TD]

[TD="bgcolor: #FFCC00"]102.5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]44000[/TD]
[TD="align: right"]3300[/TD]
[TD="align: right"]47300[/TD]

[TD="bgcolor: #FFCC00"]440[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]3500[/TD]
[TD="align: right"]466[/TD]
[TD="align: right"]3966[/TD]

[TD="bgcolor: #FFCC00"]8.75[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]6000[/TD]
[TD="align: right"]877[/TD]
[TD="align: right"]6877[/TD]

[TD="bgcolor: #FFCC00"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]11300[/TD]
[TD="align: right"]1254[/TD]
[TD="align: right"]12554[/TD]

[TD="bgcolor: #FFCC00"]28.25[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]2400[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]2520[/TD]

[TD="bgcolor: #FFCC00"]24[/TD]

</tbody>

If Type is D, & Days are in between (-) to 90 Days, then Rebate will be 0.5% of Gross value.
If Type is D, & Days are in between 91 to 180 Days, then Rebate will be 0.25% of Gross value.
If Type is D, & Days are more than 180 then Rebate will be 0.

If Type is blank & Days are in between (-) to 90 Days, then Rebate will be 1% of Gross value.
If Type is blank & Days are in between 91 to 180 Days, then Rebate will be 0.5% of Gross value.
If Type is blank, & Days are more than 180 then Rebate will be 0.

I have highlighted desired results, but only a single formula will cover up all these conditions. Pls help.

Regards
RAMU

 

[mole999]

in j2 (k2 for testing)
> =IF(AND(C2="D",(I2<=90)),F2*0.005,IF(AND(C2="D",(I2<=180)),F2*0.0025,IF(AND(C2="D",(I2>180)),F2*0,IF(AND(C2="",(I2<=90)),F2*0.01,IF(AND(C2="",(I2<=180)),F2*0.005,IF(AND(C2="",(I2>180)),F2*0,0))))))

 

[Aladin Akyurek]

Also, in J2 enter and copy down:

=VLOOKUP(I2,IF(C2="D",{-9.99E+307,0.5;91,0.25;181,0},{-9.99E+307,0.1;91,0.5;181,0}),2,1)*$F2/100

 

[RAMU]

Sir,

Woww, Gr8, but J7 is showing wrong result. it is coming 2.4 instead of 24. But if i change I7 to 91, then it is showing 12.

 

[Aladin Akyurek]

Sir,

Woww, Gr8, but I think it would be:

=VLOOKUP(I2,IF(C2="D",{-9.99E+307,0.5;91,0.25;181,0},{-9.99E+307,1;91,0.5;181,0}),2,1)*$F2/100

You are welcome.

 

[RAMU]

Dear Mole,

Bang On !!!! Gr8