Seems basic, but not working. Assistance is much appreciated. Thanks.

[ns039pn]

I am using a nested IF formula with multiple conditions that looks at two cells (D7 and C7). See below:

=IF(AND(D7=100%,C7=100%),5,IF(AND((D7=100%),AND(100%>C7,C7>94%)),4,IF(AND(D7=100%,C7<95%),3,IF(AND(D7>89%,D7<100%),2,IF(D7<90%,1,0)))))


The table that is used to qualify the resulting value is as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Resulting Value [/TD]
[TD] D7 [/TD]
[TD]C7[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]100%[/TD]
[TD]95%-99%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]100%[/TD]
[TD]Less than 95%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]90%-99% [/TD]
[TD]Any Percentage[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Less than 90%[/TD]
[TD]Any Percentage[/TD]
[/TR]
</tbody>[/TABLE]

Issue
The formula is working and results in the desired value; with the exception of the nested IF(AND(D7=100%,C7<95%),3. When D7 is 100% AND C7 is less than 95%, the resulting value should be 3. However, for some reason, when C7 = 94%, the resulting value remains 4. And when C7 is anything equal to or less than 93%, then the formula works and I get a resulting value of 3.

Any assistance is much appreciated.

 

[Dave Patton]

try

=IF(AND(C7=1,D7=1),5,IF(AND(C7>0.95,D7=1),4,IF(AND(C7<0.95,D7=1),3,IF(AND(C7>0,D7>0.9),2,IF(AND(C7>0,D7<0.9),1,0)))))

Please review. The criteria columns shown were in reverse order.

 

[Dave Patton]

=if(and(c7=1,d7=1),5,if(and(c7>=0.95,d7=1),4,if(and(c7<0.95,d7=1),3,if(and(c7>0,d7>=0.9),2,if(and(c7>0,d7<0.9),1,0)))))

 

[ns039pn]

=if(and(c7=1,d7=1),5,if(and(c7>=0.95,d7=1),4,if(and(c7<0.95,d7=1),3,if(and(c7>0,d7>=0.9),2,if(and(c7>0,d7<0.9),1,0)))))

Dave, thank you very much. The second formula worked like a charm.

 

[Dave Patton]

Thanks for the feedback. Dave