IF Function that has multiple criteria.

Helen28

New Member
Joined
Jul 3, 2018
Messages
5
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Re: Need help with IF Function that has multiple criteria.

Try this?


Book1
ABCDEF
37%8%9%10%16High Risk
47%8%10%10%14High Risk
540%41%42%43%44No Risk
641%41%42%43%41No Risk
715%12%10%10%14Medium Risk
840%41%42%43%40Medium Risk
Sheet1
Cell Formulas
RangeFormula
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
 
Upvote 0
Re: Need help with IF Function that has multiple criteria.

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

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top