IF AND Statement partially matching criteria

[sjurawan]

Hi,

I currently have the formula below looking a set of data comprised of people and percentages. The formula currently looks at if a person is in the bottom 20% and are below the target (0.815). From this it produces either a 3 if they are both below target and in the bottom 20% or a 0 if they are not in the bottom 20%, what I need to do is have the formula produce a 2 if the person is under the target (0.815) but is not in the bottom 20%. Any help is really appreciated! Thank you!

=IFERROR(IF(AND(VLOOKUP($A4,INDIRECT("'"&C$3&"'!$B:$C"),2,FALSE)<percentile.inc(indirect("'"&c$3&"'!$c$2:$c$600"),0.2),vlookup($a4,indirect("'"&c$3&"'!$b:$c"),2,false)<0.815),"3","0"),"1")*1

Kind Regards
Sam</percentile.inc(indirect("'"&c$3&"'!$c$2:$c$600"),0.2),vlookup($a4,indirect("'"&c$3&"'!$b:$c"),2,false)<0.815),"3","0"),"1")*1

 

[etaf]

how do you know if bottom 20% and how do you know target actuals

where are the ranges

IF( AND(cell range < = 20% , cell range < 0.815) , 3 , if( AND(cell range > 20% , cell range < 0.815) , 0,

however,

bottom 20% and are below the target (0.815). = 3
above 20% and are below the target (0.815). = 2
what are the rules for 0

any rules for a 1