Help in nest IF Logic Statement

[kpierce]

All -
I continue to struggle with in getting nested IF statements. I am sorry to lean on everyone so hard but I appreciate all the help. I hope to be able to contribute to this forum soon.

I need to calculate a RYGC Status (Red, Yellow, Green, Complete)

If have the following cells:
C25=Baseline Finish
D25= Finish Date (Date Field)
F25=Percentage Complete (% field)

First I need to subtract C25-D25. So if baseline finish = 6/1/11 and Finish Date is 6/11/11 the difference is 10 days.

Then I need to apply the following rules
If F25 = 100% = “C”
If difference in days is <=0 = “G”
If difference in days is >0 and <=8days = “Y”
If >8 days = “R”
If C25 or D25 are empty then show nothing.

This is making my head spin, and I appreciate the help.
Thanks

 

[jasonb75]

Try

=if(count(C25:D25)=2,if(F25=1,"C",lookup(C25-D25,{-1e100,1,9},{"G","Y","R"})),"")

Think I got that right.

 

[el_ja]

This seems to work for me (if you see any regional formatting error please forgive me I work in spanish excel)
=IF(F25=1;"C",IF(D25-C25<=0,"G",IF(AND(D25-C25>0,D25-C25<=8),"Y",IF(D25-C25>8,"R","error"))))

 

[kpierce]

jasonb75 and el_ja,

Thank you both for the input.

However I can't get either to work
Jasonb75 - this seems to always return "G"
el_ja I get an error when I try to paste your example.

Thanks again for all the help.

 

[jasonb75]

Please provide some dates where it should give something other than "G".

 

[kpierce]

Sure

C25 = Baseline Date
D25 = Actual Date
So I need to show a status based on the difference of these fields.
C25 = 6/1/11
D25 = 6/10/11
9 days overdue so = R (anything over >=8days is R)

C25 = 6/1/11
D25 = 6/2/11
1 day overdue so = Y

C25 = 6/1/11
D25 = 6/9/11
8 days overdue so = Y

C25= 6/1/11
D25 = 6/1/11
0 days overdue so = G

C25 = 6/1/11
D25 = 5/30/11
-1 days overdue so G
otherwise "G"
Basically "G" is the default.

catch is if F25 = 100%, then "C" for complete

Basically
If past due by >=8 days = "R"
past due 1 = 8 days = "Y"
otherwise "G"
if F25 = 100% = "C"

Hope this helps...

I really appreciate the help.

 

[jasonb75]

I had C25 and D25 in the wrong order, try

=IF(COUNT(C25:D25)=2,IF(F25=1,"C",LOOKUP(D25-C25,{-1E+100,1,9},{"G","Y","R"})),"")

Think that should cure it.

 

[kpierce]

simply amazing... I have no idea how this works, but it does.

Thank you so much for the help.

 

[el_ja]

=IF(F25=1,"C",IF(D25-C25<=0,"G",IF(AND(D25-C25>0,D25-C25<=8),"Y",IF(D25-C25>8,"R","error"))))

Sorry it seems that I had one semicolon instead of a comma.
But you got it now and even prettier
greets