IF Function that has multiple criteria.

[Helen28]

Hi there,

I am trying to construct an IF Function for the following problem:

Data:
5 collums of data (A,B,C,D,E) and 33 participants.



Conditions:

• define person as 'high risk' if either '3 out of 4 values (A,B,C,D) are smaller than 10' OR if '2 out of 4 values (A,B,C,D) are smaller than 10 AND E< 15'
• define person as 'high risk' if '3 out 4 values of A,B,C,D greater than 40% AND E>40'
• define person as 'medium risk' if neither 'high risk' or 'no risk'

Here is one of many attempst:

=IF(OR(COUNTIF(A3:D3,"<10%")=3,AND(COUNTIF(A3:D3,"<10%")=2,E3<15)),"High Risk",IF(COUNTIF(A3:D3,">40%")>3),AND(F3>40),"No Risk","Medium Risk")

The first part of the formula (high risk) seems to work in isolation but as soon as I try and add the other conditions I only get error messages (mainly the #value error). I have tried lots of different ways of putting it, but I cannot make it work. My excel skills are very limited and after many hours of trying I am about ready to give up. So any help would be greatly appreciated!!

Thanks!
Helen

 

[wideboydixon]

Re: Need help with IF Function that has multiple criteria.

Try this?

Book1
	A	B	C	D	E	F
3	7%	8%	9%	10%	16	High Risk
4	7%	8%	10%	10%	14	High Risk
5	40%	41%	42%	43%	44	No Risk
6	41%	41%	42%	43%	41	No Risk
7	15%	12%	10%	10%	14	Medium Risk
8	40%	41%	42%	43%	40	Medium Risk
Sheet1

Cell Formulas
Range	Formula
F3	=IF(OR(COUNTIF($A3:$D3,"<10%")>=3,AND(COUNTIF($A3:$D3,"<10%")=2,$E3<15)),"High Risk",IF(AND(COUNTIF($A3:$D3,">40%")>=3,$E3>40),"No Risk", "Medium Risk"))

WBD

 

[Helen28]

Re: Need help with IF Function that has multiple criteria.

Thank you soooo much! This was doing my head in! xxx