formula help

[miken5678]

I know you can nest a limited number of if statements but not sure what i am missing to get this work. I am ranking items based on a list but want to omit that item from being ranked if another associated cell equals 100%. I hope this makes sense

this formula works

=IF((g85<1),(G85="na",0,COUNT(G$78:G$91)-(RANK(G85,G$78:G$91)+COUNTIF(G$78:G85,G85))+2),"")

however it rates the cells with 100% and i only want to rate that cell if a value is below 100% so I tried this

IF(g85<1,(G85="na",0,COUNT(G$78:G$91)-(RANK(G85,G$78:G$91)+COUNTIF(G$78:G85,G85))+2))

and tried this

=IF((g85<1),(G85="na",0,COUNT(G$78:G$91)-(RANK(G85,G$78:G$91)+COUNTIF(G$78:G85,G85))+2)),"")

but it wont accept. g85 being a range of 0-100%. Anything below 100% i want rated.

i assume its a possible issue with paranthesis but i am not seeing it as the critiera for true false are correct.. can this not be done?

 

[qafself]

Don't know if you ever got this one sorted but I think you need

=IF(G85="na",0,IF(G85<1,COUNT(G$78:G$91)-(RANK(G85,G$78:G$91)+COUNTIF(G$78:G85,G85))+2,""))

 

[miken5678]

ok i tried that and it came up with an error and self corrected through excel to (dont ask me what the difference is and why excel caught it as i dont see it )

=IF(G85="na",0,IF(G85<1,COUNT(G$78:G$91)-(RANK(G85,G$78:G$91)+COUNTIF(G$78:G85,G85))+2,""))

it seems to work now. I appreciate your help. I will fill these down and make sure it works

Mike