IF Function that has multiple criteria.

Helen28

New Member
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

wideboydixon

Well-known Member
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
Re: Need help with IF Function that has multiple criteria.

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

Replies
3
Views
126
Replies
1
Views
101
Replies
3
Views
305
Replies
4
Views
407
Replies
2
Views
109

1,127,313
Messages
5,623,935
Members
416,001
Latest member
teabag

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.

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

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