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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Re: Need help with IF Function that has multiple criteria.

Try this?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">7%</td><td style="text-align: right;;">8%</td><td style="text-align: right;;">9%</td><td style="text-align: right;;">10%</td><td style="text-align: right;;">16</td><td style=";">High Risk</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">7%</td><td style="text-align: right;;">8%</td><td style="text-align: right;;">10%</td><td style="text-align: right;;">10%</td><td style="text-align: right;;">14</td><td style=";">High Risk</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">40%</td><td style="text-align: right;;">41%</td><td style="text-align: right;;">42%</td><td style="text-align: right;;">43%</td><td style="text-align: right;;">44</td><td style=";">No Risk</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">41%</td><td style="text-align: right;;">41%</td><td style="text-align: right;;">42%</td><td style="text-align: right;;">43%</td><td style="text-align: right;;">41</td><td style=";">No Risk</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">15%</td><td style="text-align: right;;">12%</td><td style="text-align: right;;">10%</td><td style="text-align: right;;">10%</td><td style="text-align: right;;">14</td><td style=";">Medium Risk</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">40%</td><td style="text-align: right;;">41%</td><td style="text-align: right;;">42%</td><td style="text-align: right;;">43%</td><td style="text-align: right;;">40</td><td style=";">Medium Risk</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F3</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">COUNTIF(<font color="Green">$A3:$D3,"<10%"</font>)>=3,AND(<font color="Green">COUNTIF(<font color="Purple">$A3:$D3,"<10%"</font>)=2,$E3<15</font>)</font>),"High Risk",IF(<font color="Red">AND(<font color="Green">COUNTIF(<font color="Purple">$A3:$D3,">40%"</font>)>=3,$E3>40</font>),"No Risk", "Medium Risk"</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

WBD
 

Helen28

New Member
Joined
Jul 3, 2018
Messages
5
Re: Need help with IF Function that has multiple criteria.

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

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,682
Members
415,920
Latest member
ExcelNoob28

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
Top