Multiple If Conditions

RAMU

Active Member
Joined
Dec 11, 2009
Messages
321
Dear Friends,

Working in a workbook where there are multiple if conditions:


Sheet1

ABCDEFGHIJ
1PARTY NAMEDATETYPEDOCADDRESSGROSS VALUEADVAMOUNTDays Rebate
2MAA LAXMI TRADER14-08-16DA-122 205002200227000102.5
3BISWAS STORES13-09-16 B-20 44000330047300-3440
4MAA LAXMI TRADER12-09-16DA-215 35004663966918.75
5BISWAS STORES12-12-16 B-165 600087768771810
6RAJA ENTERPRISE26-07-16DA-05 1130012541255417828.25
7BISWAS STORES22-12-16 B-183 240012025207624

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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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