# HELP! Apply a table of rules, using IF, AND and Ranges - at a bit of a loss!

#### wongmaster50

##### New Member
Guys n' Gals,

My Excel is seriously rusty and i'm a little a bit at a loss as to how to deal with the problem below.

I have 2 columns of data as outlined below, which depending on which rules they meet they are categorized as per the table below (see Example data)

There are a set of rules that i've written out (also below), which have to be applied to the values - from a logical POV each value must either be less than 0.7 or equal or greater than AND dependent on the percentage range they fall into they THEN get classified.

Any help would hugely be appreciated,

Andy

Example Data (i can't get Column R to calculate)

 Column P Column Q Column R 0.84 67 Normal 0.65 81 mild 0.75 84 normal 0.55 60 moderate 0.69 77 moderate 0.5 44 severe 0.35 29 very severe

<tbody>
</tbody>

Rules
IF AND THEN
Column A Column B Column C
 >= 0.7 0-100 Normal < 0.7 ≥ 80% Mild < 0.7 50–79% Moderate < 0.7 30–49% Severe < 0.7 < 30% Very Severe

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>

https://www.dropbox.com/s/8rgiw5yvpwwzqg4/Screen Shot 2015-11-29 at 12.36.02.png?dl=0

#### svendiamond

##### Well-known Member
=IF(P2>=0.7,"Normal",IF(Q2>=80,"Mild",IF(Q2>=50,"Moderate",IF(Q2>=30,"Severe",IF(Q2<30,"Very Severe")))))

#### Oeldere

##### Well-known Member
In the green cells the result.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">39</td><td style=";">Column P</td><td style=";">Column Q</td><td style=";">Column R</td><td style="text-align: right;;"></td><td style="background-color: #B4C6E7;;">Vlook up table</td><td style="text-align: right;background-color: #B4C6E7;;"></td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: center;;">0,84</td><td style="text-align: center;;">0,67</td><td style=";">Normal</td><td style="background-color: #92D050;;">normal</td><td style="text-align: center;;">0,00</td><td style=";">Very Severe</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: center;;">0,65</td><td style="text-align: center;;">0,81</td><td style=";">mild</td><td style="background-color: #92D050;;">Mild</td><td style="text-align: center;;">0,30</td><td style=";">Severe</td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: center;;">0,75</td><td style="text-align: center;;">0,84</td><td style=";">normal</td><td style="background-color: #92D050;;">normal</td><td style="text-align: center;;">0,50</td><td style=";">Moderate</td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: center;;">0,55</td><td style="text-align: center;;">0,60</td><td style=";">moderate</td><td style="background-color: #92D050;;">Moderate</td><td style="text-align: center;;">0,80</td><td style=";">Mild</td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: center;;">0,69</td><td style="text-align: center;;">0,77</td><td style=";">moderate</td><td style="background-color: #92D050;;">Moderate</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style="text-align: center;;">0,50</td><td style="text-align: center;;">0,44</td><td style=";">severe</td><td style="background-color: #92D050;;">Severe</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style="text-align: center;;">0,35</td><td style="text-align: center;;">0,29</td><td style=";">very severe</td><td style="background-color: #92D050;;">Very Severe</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Blad8</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><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: #DAE7F5;color: #161120">E40</th><td style="text-align:left">=IF(<font color="Blue">\$B40>=0.7,"normal",VLOOKUP(<font color="Red">\$C40,\$F\$40:\$G\$43,2,1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

#### wongmaster50

##### New Member
Guys - thanks for the speedy replies - much appreciated, will give it a whirl now - i'll let you know how i get on!

#### wongmaster50

##### New Member
Thanks - ended up using this one, worked a treat and nice and elegant. i didn't realise nested IDs would work recursively like that - thanks again

#### Oeldere

##### Well-known Member
Code:
``ended up using this one``
Which one?

#### wongmaster50

##### New Member
Code:
``ended up using this one``
Which one?

The first one (not using the VLOOKUP)

Thanks again

1,082,046
Messages
5,362,873
Members
400,696
Latest member
Kclynn

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...