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]

Try

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

 

[mrxlsx]

Thanks VoG,

It's working like a shot gun.

So we add on all the upcoming OR or AND functions in the above fashion, Right?

mrxlsx