using multiple conditions

L

lschuh

I would like to know how to write the syntax to test for
1. age>44 or 2. Seniority is >9 and by determining the answer to that
3. Arsenic exposure >4.9
4. Cadmium exposure >2.4

If 1. is true or 2. true then I need to check 3. and 4.
if 1. is true or 2. is true and 3 and 4 are false then ?
if 1 or 2 is false and 3 or 4 is false then something else

would I use and or /and condition. The way I wrote my syntax the or works
but the and does not. Any clues on how to write the syntax to check for all
4 conditions?
 
O

olasa

This is one solution:
="1 is "&PROPER(TEXT(B2>44,""))&". 2 is "&PROPER(TEXT(B3>9,""))&". 3 or
4 is "&PROPER(TEXT(AND(B4>4.9,B5>2.4),""))

The formula tests all 4 conditions, since I thought the AND/OR
conditions were not clear. I.e. If 1 or 2 is False and 3 or 4 is False
....
In case you want AND/OR conditions, I would recommend to use CHOOSE.
=CHOOSE(1+(A2>44)*1+(A3>9)*2+(AND(A4>4.9,A5>2.4))*3,"1 is False. 2. is
False. 3 or 4 is False","1 is True. 2. is False. 3 or 4......) 7
different solutions


Hope it helped
Ola Sandström
 
D

Duke Carey

Maybe

=IF(OR(age>44,seniority>0),IF(OR(aresenic>4.9,cadmium>2.4),"Exposure too
high","Exposure ok"),"Too young/low seniority")
 
L

lschuh

Part of that worked. I will paste my formul
=IF(OR($F3>44,$G3>9),IF(OR($I3>2.5,$K3>2.5,$M3>2.5,$O3>2.5,$Q3>2.5,$J3>5,$L3>5,$N3>5,$P3>5,$S3>5),1,0))

This works if the condition is true. If it is not I get "false". Not the 0
or 1 I am looking for.

Not to make it too complicated I will break it down.
If the first OR comes back with true and the second OR comes back with true
then the employee gets a semi physical
If the first OR comes back false and the second OR comes back true then the
employee gets an annual physical
If the first and second OR come back false then the employee doesn't get a
physical.
If the first OR comes back true but the second OR is false then the employee
doesn't get a physical

Both of the first and second or conditions must be met to get the semi.
The first condition can be met but the second condition is not, then none.
The first condition is not met and the second is not met then none
The second condition is met and the first is not then annual.

Does that make it any clearer?
 
L

lschuh

The formula I finally got to work is as follows
=IF(OR($F3>44,$G3>9),1,0)*IF(OR($I3>2.5,$K3>2.5,$M3>2.5,$O3>2.5,$Q3>2.5,$J3>5,$L3>5,$N3>5,$P3>5,$S3>5),1,0)
thanks to all who responded.
 
Top