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

[wongmaster50]

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,

Many Thanks in advance
Andy


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

[TABLE="width: 636"]
<tbody>[TR]
[TD="align: center"]Column P[/TD]
[TD="align: center"]Column Q[/TD]
[TD="align: center"]Column R[/TD]
[/TR]
[TR]
[TD="align: center"]0.84[/TD]
[TD="align: center"]67[/TD]
[TD="align: center"]Normal[/TD]
[/TR]
[TR]
[TD="align: center"]0.65[/TD]
[TD="align: center"]81[/TD]
[TD="align: center"]mild[/TD]
[/TR]
[TR]
[TD="align: center"]0.75[/TD]
[TD="align: center"]84[/TD]
[TD="align: center"]normal[/TD]
[/TR]
[TR]
[TD="align: center"]0.55[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]moderate[/TD]
[/TR]
[TR]
[TD="align: center"]0.69[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]moderate[/TD]
[/TR]
[TR]
[TD="align: center"]0.5[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"]severe[/TD]
[/TR]
[TR]
[TD="align: center"]0.35[/TD]
[TD="align: center"]29[/TD]
[TD] very severe[/TD]
[/TR]
</tbody>[/TABLE]

Rules
IF AND THEN
Column A Column B Column C
[TABLE="width: 636"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]>= 0.7[/TD]
[TD="align: center"]0-100[/TD]
[TD="align: center"]Normal[/TD]
[/TR]
[TR]
[TD="align: center"]< 0.7[/TD]
[TD="align: center"]≥ 80%[/TD]
[TD="align: center"]Mild[/TD]
[/TR]
[TR]
[TD="align: center"]< 0.7[/TD]
[TD="align: center"]50–79%[/TD]
[TD="align: center"]Moderate[/TD]
[/TR]
[TR]
[TD="align: center"]< 0.7[/TD]
[TD="align: center"]30–49%[/TD]
[TD="align: center"]Severe[/TD]
[/TR]
[TR]
[TD="align: center"]< 0.7[/TD]
[TD="align: center"]< 30%[/TD]
[TD="align: center"]Very Severe[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[svendiamond]

=IF(P2>=0.7,"Normal",IF(Q2>=80,"Mild",IF(Q2>=50,"Moderate",IF(Q2>=30,"Severe",IF(Q2<30,"Very Severe")))))

 

[Oeldere]

In the green cells the result.

Book1
	B	C	D	E	F	G
39	Column P	Column Q	Column R		Vlook up table
40	0,84	0,67	Normal	normal	0,00	Very Severe
41	0,65	0,81	mild	Mild	0,30	Severe
42	0,75	0,84	normal	normal	0,50	Moderate
43	0,55	0,60	moderate	Moderate	0,80	Mild
44	0,69	0,77	moderate	Moderate
45	0,50	0,44	severe	Severe
46	0,35	0,29	very severe	Very Severe
Blad8

Cell Formulas
Range	Formula
E40	=IF($B40>=0.7,"normal",VLOOKUP($C40,$F$40:$G$43,2,1))

 

[wongmaster50]

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

 

[wongmaster50]

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]

ended up using this one

Which one?

Since you got 2 answers on your question.

 

[wongmaster50]

ended up using this one

Which one?

Since you got 2 answers on your question.

The first one (not using the VLOOKUP)

Thanks again