Please help with IIf formula #ERROR in task field

[Caroline Gormley, PMP, MCT]

I've worked out all of my logic across several custom task fields, but can't
get the final step to work. I have three fields: Figure15, Figure16, and
Figure17. Each has graphic indicators set to 1=Red, 2=Yellow, and 3=Green.
The logic in my Summary field should be if Figure16=1, then Summary=Red; if
Figure15=2, then Summary=Yellow; and if Field17=Green, then Summary is Green.

I've triend several variations on the theme, and the closest that I can get
is:
IIf([Number16]=1,1,IIf([Number15]=2,2,3))

however where Figure15=2 so that Summary should be Yellow, I get #ERROR
instead. The Red and the Green appear properly.

Please help.

 

[Caroline Gormley, PMP, MCT]

I meant Number15, Number16, and Number17 - not sure why I said Figure. I'm
still working on this and have been trying now to use Switch but with it, I
can only get the Red indicator to appear.

Switch([Number16]=1,1,[Number15]=2,2,[Number17]=3,3)

Any help will be appreciated, as I really need to solve this.

 

[Dale Howard [MVP]]

Caroline --

I am sitting here, scratching my head, trying to figure out what your
Number15, Number16, and Number17 fields actually do. I assume they are Task
fields, but what are the new names you have given them, and what does the
data represent in each field? And the fourth field that calculates a
summary of the other three fields, what is the new name you have given it?
I have tried simulating your situation, and I don't get the #Error message
at all. Let us know and we will try to help you.




"Caroline Gormley, PMP, MCT"

I meant Number15, Number16, and Number17 - not sure why I said Figure. I'm
still working on this and have been trying now to use Switch but with it,
I
can only get the Red indicator to appear.

Switch([Number16]=1,1,[Number15]=2,2,[Number17]=3,3)

Any help will be appreciated, as I really need to solve this.

I've worked out all of my logic across several custom task fields, but
can't
get the final step to work. I have three fields: Figure15, Figure16, and
Figure17. Each has graphic indicators set to 1=Red, 2=Yellow, and
3=Green.
The logic in my Summary field should be if Figure16=1, then Summary=Red;
if
Figure15=2, then Summary=Yellow; and if Field17=Green, then Summary is
Green.

I've triend several variations on the theme, and the closest that I can
get
is:
IIf([Number16]=1,1,IIf([Number15]=2,2,3))

however where Figure15=2 so that Summary should be Yellow, I get #ERROR
instead. The Red and the Green appear properly.

Please help.

 

[Dale Howard [MVP]]

Caroline --

Please send me the file at:

dale[DOT]howard[AT]msprojectexperts[DOT]com

I will take a look at it and see if I can make sense of what you are doing.
Thanks!




"Caroline Gormley, PMP, MCT"

Here is a summary of my custom fields. Is there any way to send you the
project file?

Baseline Indicator(Date1)=[Baseline Finish]……if no baseline “-“
Finish Variance Indicator(Number10)=[Finish Variance]……if >=0, green; if

0,

red
Schedule Slippage(Number12)=[Finish Variance]/[Baseline Duration]……if >
.5,
red; if >0, yellow; if <=0, green
Finish Date Change(Number13)=[Finish1]-[Finish] where Finish1 is
populated
from last month’s dates……if =0, green, if not=0, red
% Complete Indicator(Number11)=[% Complete]……if =100, green
Scope Change(Flag 10)=Manual entry Yes/No
Scope Change Indicator(Flag11)=[Flag10]……if Yes, yellow
Resource Change(Flag12)=Manual entry Yes/No
Resource Change Indicator(Flag 13)=[Flag12]…..if Yes, yellow

Summary Red(Number16) desired logic = If % Complete does not equal 100
AND If Schedule Slippage is greater than 50
Summary Red(Number16)=IIf([% Complete]<100 And [Number12]>0.5,1,2)....if
1,
Red

Summary Yellow(Number15) desired logic = If Finish Date Change does not
equal 0
OR
If Resource Change is Yes
OR
If Scope Change is Yes
Summary Yellow(Number15)=IIf([Number13]<>0 Or [Flag12]=Yes Or
[Flag10]=Yes,2,3)

Summary Green(Number17) desired logic = IIf([% Complete]=100 Or [Finish
Variance]=0,3,2)
Summary Green(Number17)=IIf([% Complete]=100 Or [Finish Variance]=0,3,2)

I hope this helps!

Caroline --

I am sitting here, scratching my head, trying to figure out what your
Number15, Number16, and Number17 fields actually do. I assume they are
Task
fields, but what are the new names you have given them, and what does the
data represent in each field? And the fourth field that calculates a
summary of the other three fields, what is the new name you have given
it?
I have tried simulating your situation, and I don't get the #Error
message
at all. Let us know and we will try to help you.




"Caroline Gormley, PMP, MCT"

I meant Number15, Number16, and Number17 - not sure why I said Figure.
I'm
still working on this and have been trying now to use Switch but with
it,
I
can only get the Red indicator to appear.

Switch([Number16]=1,1,[Number15]=2,2,[Number17]=3,3)

Any help will be appreciated, as I really need to solve this.

:

I've worked out all of my logic across several custom task fields, but
can't
get the final step to work. I have three fields: Figure15, Figure16,
and
Figure17. Each has graphic indicators set to 1=Red, 2=Yellow, and
3=Green.
The logic in my Summary field should be if Figure16=1, then
Summary=Red;
if
Figure15=2, then Summary=Yellow; and if Field17=Green, then Summary is
Green.

I've triend several variations on the theme, and the closest that I
can
get
is:
IIf([Number16]=1,1,IIf([Number15]=2,2,3))

however where Figure15=2 so that Summary should be Yellow, I get
#ERROR
instead. The Red and the Green appear properly.

Please help.

 

[Gertie]

Thanks for the help, we were able to get it to work. It turned out that there
was an error in one of the underlying calculations that led to these final
equations.

Caroline --

Please send me the file at:

dale[DOT]howard[AT]msprojectexperts[DOT]com

I will take a look at it and see if I can make sense of what you are doing.
Thanks!




"Caroline Gormley, PMP, MCT"

Here is a summary of my custom fields. Is there any way to send you the
project file?

Baseline Indicator(Date1)=[Baseline Finish]……if no baseline “-“
Finish Variance Indicator(Number10)=[Finish Variance]……if >=0, green; if

0,

red
Schedule Slippage(Number12)=[Finish Variance]/[Baseline Duration]……if >
.5,
red; if >0, yellow; if <=0, green
Finish Date Change(Number13)=[Finish1]-[Finish] where Finish1 is
populated
from last month’s dates……if =0, green, if not=0, red
% Complete Indicator(Number11)=[% Complete]……if =100, green
Scope Change(Flag 10)=Manual entry Yes/No
Scope Change Indicator(Flag11)=[Flag10]……if Yes, yellow
Resource Change(Flag12)=Manual entry Yes/No
Resource Change Indicator(Flag 13)=[Flag12]…..if Yes, yellow

Summary Red(Number16) desired logic = If % Complete does not equal 100
AND If Schedule Slippage is greater than 50
Summary Red(Number16)=IIf([% Complete]<100 And [Number12]>0.5,1,2)....if
1,
Red

Summary Yellow(Number15) desired logic = If Finish Date Change does not
equal 0
OR
If Resource Change is Yes
OR
If Scope Change is Yes
Summary Yellow(Number15)=IIf([Number13]<>0 Or [Flag12]=Yes Or
[Flag10]=Yes,2,3)

Summary Green(Number17) desired logic = IIf([% Complete]=100 Or [Finish
Variance]=0,3,2)
Summary Green(Number17)=IIf([% Complete]=100 Or [Finish Variance]=0,3,2)

I hope this helps!

Caroline --

I am sitting here, scratching my head, trying to figure out what your
Number15, Number16, and Number17 fields actually do. I assume they are
Task
fields, but what are the new names you have given them, and what does the
data represent in each field? And the fourth field that calculates a
summary of the other three fields, what is the new name you have given
it?
I have tried simulating your situation, and I don't get the #Error
message
at all. Let us know and we will try to help you.




"Caroline Gormley, PMP, MCT"
I meant Number15, Number16, and Number17 - not sure why I said Figure.
I'm
still working on this and have been trying now to use Switch but with
it,
I
can only get the Red indicator to appear.

Switch([Number16]=1,1,[Number15]=2,2,[Number17]=3,3)

Any help will be appreciated, as I really need to solve this.

:

I've worked out all of my logic across several custom task fields, but
can't
get the final step to work. I have three fields: Figure15, Figure16,
and
Figure17. Each has graphic indicators set to 1=Red, 2=Yellow, and
3=Green.
The logic in my Summary field should be if Figure16=1, then
Summary=Red;
if
Figure15=2, then Summary=Yellow; and if Field17=Green, then Summary is
Green.

I've triend several variations on the theme, and the closest that I
can
get
is:
IIf([Number16]=1,1,IIf([Number15]=2,2,3))

however where Figure15=2 so that Summary should be Yellow, I get
#ERROR
instead. The Red and the Green appear properly.

Please help.