Multiple If Conditions

RAMU

Active Member
Joined
Dec 11, 2009
Messages
345
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
Dear Friends,

Working in a workbook where there are multiple if conditions:


Sheet1

ABCDEFGHIJ
MAA LAXMI TRADER14-08-16DA-122 0
BISWAS STORES13-09-16 B-20 -3
MAA LAXMI TRADER12-09-16DA-215 91
BISWAS STORES12-12-16 B-165 181
RAJA ENTERPRISE26-07-16DA-05 178
BISWAS STORES22-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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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))))))
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
Dear Mole,

Bang On !!!! Gr8
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top