Graphical indicators on CV% and SV%

[Nikou]

Hi,

I am trying to put a graphical indicator on CV% and SV% values. The
thresholds are: any variance percentage less or equal to 5% is Green, between
5% to 10% (included) is Yellow, and more that 10% variance is Red. The
formula I have is this:

IIf([CV %>]>=-5And[CV%]<=5,â€Greenâ€,IIf [CV%]>5 And
[CV%]<=10,â€Yellowâ€,IIf[CV%]<-5And [CV%]>-10,â€Yellow,â€Redâ€)))

Unfortunately it doesn't return the right graphical indicator. For example
for CV% = -0.05% (green status) and CV%= -5.64% (yellow status), it is
returning "Red".

Am I using the right formula? What is it that I am doing wrong? Any thought
is appreciated.

 

[Jim Aksel]

It's easier if you use the Absolute Value function as well becuase it takes
care of the signs for you. Try this:

IIf(abs([SV%])<=5,"Green",IIf(abs([SV%])<=10,"Yellow","Red"))

You can substiture CV for SV as well.

This formula provides

CV% +/- 5% = Green
CV% +/- 10% = Yellow
Else Red
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

Check out my blog for more information:
http://www.msprojectblog.com

 

[JulieS]

Hi Nikou,

I'm a bit confused by your criteria. Is the following what you are
attempting to say?

If a task has a variance between -4.999% and 4.999% it is green.
If a task variance is between than -5% and -10% or between 5% and
10%, the task is yellow
If a task variance is greater than -10% or greater than 10%, it is
red.


The formula below worked fine in a text field:


IIf([CV%]>-5 And [CV%]<5,"Green",IIf([CV%]>=-10 And
[CV%]<=10,"Yellow","Red"))

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[Nikou]

Thanks Jim forthe helpful comment!

It's easier if you use the Absolute Value function as well becuase it takes
care of the signs for you. Try this:

IIf(abs([SV%])<=5,"Green",IIf(abs([SV%])<=10,"Yellow","Red"))

You can substiture CV for SV as well.

This formula provides

CV% +/- 5% = Green
CV% +/- 10% = Yellow
Else Red
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

Check out my blog for more information:
http://www.msprojectblog.com

Hi,

I am trying to put a graphical indicator on CV% and SV% values. The
thresholds are: any variance percentage less or equal to 5% is Green, between
5% to 10% (included) is Yellow, and more that 10% variance is Red. The
formula I have is this:

IIf([CV %>]>=-5And[CV%]<=5,â€Greenâ€,IIf [CV%]>5 And
[CV%]<=10,â€Yellowâ€,IIf[CV%]<-5And [CV%]>-10,â€Yellow,â€Redâ€)))

Unfortunately it doesn't return the right graphical indicator. For example
for CV% = -0.05% (green status) and CV%= -5.64% (yellow status), it is
returning "Red".

Am I using the right formula? What is it that I am doing wrong? Any thought
is appreciated.

 

[Nikou]

Thanks Julie!

Hi Nikou,

I'm a bit confused by your criteria. Is the following what you are
attempting to say?

If a task has a variance between -4.999% and 4.999% it is green.
If a task variance is between than -5% and -10% or between 5% and
10%, the task is yellow
If a task variance is greater than -10% or greater than 10%, it is
red.


The formula below worked fine in a text field:


IIf([CV%]>-5 And [CV%]<5,"Green",IIf([CV%]>=-10 And
[CV%]<=10,"Yellow","Red"))

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

Hi,

I am trying to put a graphical indicator on CV% and SV% values.
The
thresholds are: any variance percentage less or equal to 5% is
Green, between
5% to 10% (included) is Yellow, and more that 10% variance is Red.
The
formula I have is this:

IIf([CV %>]>=-5And[CV%]<=5,"Green",IIf [CV%]>5 And
[CV%]<=10,"Yellow",IIf[CV%]<-5And [CV%]>-10,"Yellow,"Red")))

Unfortunately it doesn't return the right graphical indicator. For
example
for CV% = -0.05% (green status) and CV%= -5.64% (yellow status),
it is
returning "Red".

Am I using the right formula? What is it that I am doing wrong?
Any thought
is appreciated.

 

[JulieS]

You're welcome Nikou. Glad to have helped and thanks for the
feedback.
Julie

Thanks Julie!

Hi Nikou,

I'm a bit confused by your criteria. Is the following what you
are
attempting to say?

If a task has a variance between -4.999% and 4.999% it is green.
If a task variance is between than -5% and -10% or between 5% and
10%, the task is yellow
If a task variance is greater than -10% or greater than 10%, it
is
red.


The formula below worked fine in a text field:


IIf([CV%]>-5 And [CV%]<5,"Green",IIf([CV%]>=-10 And
[CV%]<=10,"Yellow","Red"))

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

Hi,

I am trying to put a graphical indicator on CV% and SV% values.
The
thresholds are: any variance percentage less or equal to 5% is
Green, between
5% to 10% (included) is Yellow, and more that 10% variance is
Red.
The
formula I have is this:

IIf([CV %>]>=-5And[CV%]<=5,"Green",IIf [CV%]>5 And
[CV%]<=10,"Yellow",IIf[CV%]<-5And [CV%]>-10,"Yellow,"Red")))

Unfortunately it doesn't return the right graphical indicator.
For
example
for CV% = -0.05% (green status) and CV%= -5.64% (yellow
status),
it is
returning "Red".

Am I using the right formula? What is it that I am doing wrong?
Any thought
is appreciated.