# Solved: Conditional formula

Discussion in 'Business Applications' started by fitzowusu, Feb 23, 2013.

Not open for further replies.

Joined:
Feb 23, 2013
Messages:
6
pls i need help on this formula in Excel
I want to create a formula to suite a criteria that i am using to diagnose metabolic syndrome but i get stuck any time i input the formula.
this is the argument:
B2=1.4
M2=95
D2=1.74
P2=137
Q2=89
F2=10.3
if B2 is less than 1.2, D2 is greater than 1.8 and M2 is greater than 85, then we conclude positive

2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
so do you want to add positive to a cell
so in the cell you want to display positive or negative put
Code:
`=IF( AND(B2>1.2, D2>1.8, M2>85), "Positive", "Negative")`
Note - this does not include = or greater than so if you want = change to

Code:
`=IF(AND(B2>=1.2,D2>=1.8,M2>=85),"Positive","Negative")`
if you want a conditional format - so the cell changs colour or other format - let me know

just to check
All three need to be higher then threshold - or just one ?

see attached D5 has > or =
D6 has just >

#### Attached Files:

• ###### MetSyn.xlsx
File size:
9 KB
Views:
44

Joined:
Feb 23, 2013
Messages:
6
pls i really appreciate this help u are giving me but if i encounter any other problem i will let u know.

4. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
your welcome if that all works out for you -
You can mark your own threads solved using the button at the top of the page of the thread in the upper left corner.

Joined:
Feb 23, 2013
Messages:
6
Please this is the whole situation:
Lets say formula

A=IF(AND(B12<1.29,D12>=1.7,M12>80),"Positive","Negative")
B=IF(AND(R21>=215,D2>=1.7,M2>80),"Positive","Negative")
C=IF(AND(M2>80,F2>5.6,D2>=1.7),"Positive","Negative")
D=IF(AND(B2<1.29,R2>=215,M2>80),"Positive","Negative")
E=IF(AND(B2<1.29,F2>5.6,M2>80),"Positive","Negative")
F=IF(AND(F2>5.6,R2>=215,M2>80),"Positive","Negative")

now i want to combine these conditions to make one diagnosis as MetS+ or MetS-
thus
IF the results of either one or more of the formulas above is positive, then the final result is MetS+ or if none of the result is positive, then the result is MetS -

File size:
10 KB
Views:
44
6. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
so they are all OR

=IF( OR(
AND(B12<1.29,D12>=1.7,M12>80),
AND(R21>=215,D2>=1.7,M2>80),
AND(M2>80,F2>5.6,D2>=1.7),
AND(B2<1.29,R2>=215,M2>80),
AND(B2<1.29,F2>5.6,M2>80),
AND(F2>5.6,R2>=215,M2>80)
),
"MetS+",
"MetS-")

Code:
```=IF(OR(AND(B12<1.29,D12>=1.7,M12>80),AND(R21>=215,D2>=1.7,M2>80),AND(M2>80,F2>5.6,D2>=1.7),AND(B2<1.29,R2>=215,M2>80),AND(B2<1.29,F2>5.6,M2>80),AND(F2>5.6,R2>=215,M2>80)),"MetS+","MetS-")
```

Joined:
Feb 23, 2013
Messages:
6
Thank u very much the first formula worked but
i ALSO USED THIS FOR THE SAME DATA ON THE ATTACHED FILE BUT I GOT name error:

=IF(OR(AND(M2>80,D2>=1.7,B2&#706;1.3),
AND(M2>80,D2>=1.7,F2>5.6),
AND(M2>80,D2>=1.7,R2>=215),
AND(M2>80,B2&#706;1.3,R2>=215),
AND(M2>80,B2&#706;1.3,F2>5.6),
AND(M2>80,R2>=215,F2>5.6),
AND(D2>=1.7,B2&#706;1.3,R2>=215),
AND(D2>=1.7,B2&#706;1.3,F2>5.6),
AND(D2>=1.7,F2>5.6,R2>=215),
AND(B2&#706;1.3,F2>5.6,R2>=215)
),"MetS+","MetS-")

Pls can u help me to know what is wrong here?

8. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
for some reason the first expression returns #name
AND(M2>80,D2>=1.7,B2<1.3)

i typed it in again and copied into the main formula and it worked

it seems to have an issue with the B2 part

this now works
Code:
`=IF(OR(AND(M2>80,D2>=1.7,B2<1.3),AND(R21>=215,D2>=1.7,M2>80),AND(M2>80,F2>5.6,D2>=1.7),AND(B2<1.29,R2>=215,M2>80),AND(B2<1.29,F2>5.6,M2>80),AND(F2>5.6,R2>=215,M2>80)),"MetS+","MetS-")`
strange no idea what part it did not like

Joined:
Feb 23, 2013
Messages:
6
=IF(OR(AND(B3<1.29,D3>=1.7,M3>80),AND(M3>80,D3>=1.7,F3>5.6),AND(M3>80,D3>=1.7,R3>=215),AND(B3<1.3,R3>=215,M3>80),AND(M3>80,B3<1.3,F3>5.6),AND(F3>5.6,R3>=215,M3>80),AND(B3<1.3,R3>=215,D3>=1.7),AND(D3>=1.7,B3<1.3,F3>5.6),AND(D3>=1.7,F3>5.6,R3>=215),AND(B3<1.3,F3>5.6,R3>=215)),"MetS+","MetS-")
i used this formula and it worked
i only changed all the ">" signs with an existing one from the previous formula and it worked.
it might have been that the sign was not recognized by excel. so using an already existing one from previous formula worked.
THANK YOU VERY MUCH FOR YOUR SUPPORT.

10. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
is this a typo i introduced

Joined:
Feb 23, 2013
Messages:
6
no, it might have been from me.
But how is that possible?

12. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
just seen it was < here post #7

As Seen On