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