Issues with IF AND OR COMBINATION

[mrxlsx]

Hello Everybody,

Good evening.

My Columns are as follows:

A1-Criminal Name, B1-Crime, C1-Age, D1-Ratings, E1-Punishment

In the punishment column one will get 22 years life imprisonment if he fulfills the following conditions.

1. He must have done Rape OR Murder

AND

2. His Age should be >30

AND

3. His Ratings should be>8

It should throw 22 years in the Punishment column only if the above conditions are met otherwise it should be Nil.


More Info on this:

1. Crime column includes Murder, Rape, Robbery, Assault, Kidnap etc
2. Age column ranging from 22-75 years.
3. Ratings column ranging from 1-10 points
4. There are 3400 records we have in the list

How to write an IF AND OR combination formula for this ?

You are welcome for any other info you need on this.

Thank you
mrxlsx

 

[VoG]

Perhaps

=IF(AND(OR(B2="Rape",B2="Murder"),C1>30,D1>8),22,0)

Remark: this seems a strange system of sentencing!

 

[mrxlsx]

Ahahahaa,

Firstly thank you.

Reply to the remark: This is a dummy data. It's just for the sake of confidentiality. Actual data goes on similar lines.

Anyways, I checked it but it showing 22 years only for the Murder cases but it is leaving Rape cases though it fulfills the remaining conditions.

mrxlsx

 

[VoG]

Sorry, it should be

=IF(AND(OR(B2="Rape",B2="Murder"),C2>30,D2>8),22,0)

 

[mrxlsx]

Hi VoG,

I corrected it even if you have given me the incorrect cell ref in the first instance itself.

But somehow the formula is not throwing up the result for another option in the OR choice.

It is showing only for one option i.e. For either Murder or Rape, it is leaving the other case.

Still the same issue.

mrxlxs

 

[VoG]

It seems to work here

Excel Workbook
	B	C	D	E	F
2	Murder	31	9		22
3	Rape	32	10		22
4	Robbery	33	11		0
5	Rape	22	8		0
Sheet1

 

[mrxlsx]

Hi VoG,

Can I mail you my data?

mrxlsx

 

[VoG]

You can paste it in a reply but maybe you have spaces in your data - try

=IF(AND(OR(TRIM(B2)="Rape",TRIM(B2)="Murder"),C2>30,D2>8),22,0)

 

[mrxlsx]

Hey VoG,

Thank you,

I am sorry I have spaces in some of my data, I used TRIM and CLEAN. Now it's fine.

You know, I was trying this formula for the last one week. Real shame on me.


Sorry for bugging you.. Thank you

mrxlsx

 

[mrxlsx]

Hi VoG,

Again a small doubt,

If we have to add one more column named Status which has choices A,B,C,D,E,F.

And we have to take only if it is A OR B for the validation,

How can we add one more OR condition to the existing condition and where we fit this argument?

So now finally we have the following 4 conditions:

1. He must have done Rape OR Murder

AND

2. His Age should be >30

AND

3. His Ratings should be>8

AND

4. Status should be A OR B

If the above conditions are met then he will be sentenced to 22 yrs.

Now where we fit the argument in the formula?

Thank you
mrxlsx