Variance Formula incorporating multiple baselines needed

[C.J.]

Does anyone have information on how to incorporate multiple baselines into a
schedule status (finish variance) formula for Project Standard/Professional
2003?

I currently have the following formula set up to calculate schedule status
against the baseline with graphical indicators:
IIf((([Baseline Start]=4294967295) Or ([Baseline Finish]=4294967295)),"No
BL",IIf((([Finish Variance]/480)>=5),"Red",IIf((([Finish
Variance]/480)>0),"Yellow","Green")))

I want to create a formula that looks at the most recent baseline to
calculate the schedule status. For example, If there is a Baseline 6, then
calculate the schedule variance and graphical indicators from Baseline 6, but
if there is no baseline 6, look for baseline 5 and so on all the way down to
baseline.

I've tried multiple versions of the formula below and continue to get a
syntax error. Any suggestions?
IIf((([Baseline6 Start]=4294967295) Or ([Baseline6
Finish]=4294967295)),[Baseline5 Start],IIf(([Baseline5 Start]=4294967295) Or
([Baseline5 Finish]=4294967295)),[Baseline4 Start],IIf(([Baseline4
Start]=4294967295) Or ([Baseline4 Finish]=4294967295)),[Baseline3
Start],IIf(([Baseline3 Start]=4294967295) Or ([Baseline3
Finish]=4294967295)),[Baseline2 Start],IIf(([Baseline2 Start]=4294967295) Or
([Baseline2 Finish]=4294967295)),[Baseline1 Start],IIf(([Baseline1
Start]=4294967295) Or ([Baseline1 Finish]=4294967295)),[Baseline
Start],IIf(([Baseline Start]=4294967295) Or ([Baseline
Finish]=4294967295)),"No BL",IIf((([Finish
Variance]/480)>=5),"Red",IIf((([Finish Variance]/480)>0),"Yellow","Green")))

 

[Rod Gill]

Hi,
The value NA is a long integer-1 so I always test for >100000 since 100000
represents a date way past even my kids lifetime! Try also to build the iif
statement one stage at a time so that you know where the error is (Last bit
added).

 

[JackD]

You could save a few characters by testing for 99999 instead. With a long
formula like that you may run out of space.

--
-Jack ... For project information and macro examples visit
http://masamiki.com/project

..

Hi,
The value NA is a long integer-1 so I always test for >100000 since 100000
represents a date way past even my kids lifetime! Try also to build the iif
statement one stage at a time so that you know where the error is (Last bit
added).

--

Rod Gill
Project MVP

Does anyone have information on how to incorporate multiple baselines into
a
schedule status (finish variance) formula for Project
Standard/Professional
2003?

I currently have the following formula set up to calculate schedule status
against the baseline with graphical indicators:
IIf((([Baseline Start]=4294967295) Or ([Baseline Finish]=4294967295)),"No
BL",IIf((([Finish Variance]/480)>=5),"Red",IIf((([Finish
Variance]/480)>0),"Yellow","Green")))

I want to create a formula that looks at the most recent baseline to
calculate the schedule status. For example, If there is a Baseline 6,
then
calculate the schedule variance and graphical indicators from Baseline 6,
but
if there is no baseline 6, look for baseline 5 and so on all the way down
to
baseline.

I've tried multiple versions of the formula below and continue to get a
syntax error. Any suggestions?
IIf((([Baseline6 Start]=4294967295) Or ([Baseline6
Finish]=4294967295)),[Baseline5 Start],IIf(([Baseline5 Start]=4294967295)
Or
([Baseline5 Finish]=4294967295)),[Baseline4 Start],IIf(([Baseline4
Start]=4294967295) Or ([Baseline4 Finish]=4294967295)),[Baseline3
Start],IIf(([Baseline3 Start]=4294967295) Or ([Baseline3
Finish]=4294967295)),[Baseline2 Start],IIf(([Baseline2 Start]=4294967295)
Or
([Baseline2 Finish]=4294967295)),[Baseline1 Start],IIf(([Baseline1
Start]=4294967295) Or ([Baseline1 Finish]=4294967295)),[Baseline
Start],IIf(([Baseline Start]=4294967295) Or ([Baseline
Finish]=4294967295)),"No BL",IIf((([Finish
Variance]/480)>=5),"Red",IIf((([Finish
Variance]/480)>0),"Yellow","Green")))

 

[Pratta]

Hi CJ,
I suggest you check for no baseline first using >50000 for Baseline NA and I
think you could simplify the logic by doing that. Also, I use current date
(reporting date) as the compare date. Also, why check for baseline start and
baseline finish. Just one or the other. I check for an active baseline finish
and compare . Whilst I am not checking for multi baselines perhaps use the
following as a guide.(where 0 = no basline, 1 = black (completed), 2 = green,
3= yellow, 4 = Red):
IIf([Baseline Finish]>50000,0,IIf([Baseline Finish]<=([Current Date]-7) And
[% Complete]<100,4,IIf([Baseline Finish]<=[Current Date] And [%
Complete]<100,3,IIf([Baseline Finish]>=[Current Date] And [%
Complete]<100,2,1))))

Good luck with it...............Pratta

You could save a few characters by testing for 99999 instead. With a long
formula like that you may run out of space.

--
-Jack ... For project information and macro examples visit
http://masamiki.com/project

..

Hi,
The value NA is a long integer-1 so I always test for >100000 since 100000
represents a date way past even my kids lifetime! Try also to build the iif
statement one stage at a time so that you know where the error is (Last bit
added).

--

Rod Gill
Project MVP

Does anyone have information on how to incorporate multiple baselines into
a
schedule status (finish variance) formula for Project
Standard/Professional
2003?

I currently have the following formula set up to calculate schedule status
against the baseline with graphical indicators:
IIf((([Baseline Start]=4294967295) Or ([Baseline Finish]=4294967295)),"No
BL",IIf((([Finish Variance]/480)>=5),"Red",IIf((([Finish
Variance]/480)>0),"Yellow","Green")))

I want to create a formula that looks at the most recent baseline to
calculate the schedule status. For example, If there is a Baseline 6,
then
calculate the schedule variance and graphical indicators from Baseline 6,
but
if there is no baseline 6, look for baseline 5 and so on all the way down
to
baseline.

I've tried multiple versions of the formula below and continue to get a
syntax error. Any suggestions?
IIf((([Baseline6 Start]=4294967295) Or ([Baseline6
Finish]=4294967295)),[Baseline5 Start],IIf(([Baseline5 Start]=4294967295)
Or
([Baseline5 Finish]=4294967295)),[Baseline4 Start],IIf(([Baseline4
Start]=4294967295) Or ([Baseline4 Finish]=4294967295)),[Baseline3
Start],IIf(([Baseline3 Start]=4294967295) Or ([Baseline3
Finish]=4294967295)),[Baseline2 Start],IIf(([Baseline2 Start]=4294967295)
Or
([Baseline2 Finish]=4294967295)),[Baseline1 Start],IIf(([Baseline1
Start]=4294967295) Or ([Baseline1 Finish]=4294967295)),[Baseline
Start],IIf(([Baseline Start]=4294967295) Or ([Baseline
Finish]=4294967295)),"No BL",IIf((([Finish
Variance]/480)>=5),"Red",IIf((([Finish
Variance]/480)>0),"Yellow","Green")))