Custom field formula to evaluate a flag, and then calculate a rate

B

Barb PMO

My company uses Project Pro 2003 and Project Server

Thanks for yesterdays help on how to get a formula to multiply Baseline Work
* Project Sale Price to return the Baseline Hourly Rate. That works perfectly
now (Thanks, Marc Soester!)

Now, I need to use an Iif function to evaluate a project flag (Canadian or
US dollar contract) and if Canadian, multiply the result by the exchange
factor. Here's the formula I entered:

IIf([Enterprise Project Flag1]="Yes",([Enterprise Project Cost1]/[Baseline
Work]*60)*1.145,([Enterprise Project Cost1]/[Baseline Work])*60)

The dialog box accepts the formula, but the Canadian projects are still
showing the USD hourly rate, even though I have set the flag on those
projects to Yes.

Any ideas?
 
M

Marc Soester

Hi Barb PMO,

Project flags dont understand "yes" or "no". You will need to use
true for "yes" or
false for "no".

If you try the following it should work:

IIf([Enterprise Project Flag1]=true,([Enterprise Project Cost1]/[Baseline
Work]*60)*1.145,([Enterprise Project Cost1]/[Baseline Work])*60)

Hope it helps
 
B

Barb PMO

I tried replacing the "Yes" with true, but the results are still the same. I
checked the USD projects to make sure I didn't have it reversed to no avail.

Any other ideas?
--
Barb PMO


Marc Soester said:
Hi Barb PMO,

Project flags dont understand "yes" or "no". You will need to use
true for "yes" or
false for "no".

If you try the following it should work:

IIf([Enterprise Project Flag1]=true,([Enterprise Project Cost1]/[Baseline
Work]*60)*1.145,([Enterprise Project Cost1]/[Baseline Work])*60)

Hope it helps

--
Marc Soester
State Manager: EPM


Barb PMO said:
My company uses Project Pro 2003 and Project Server

Thanks for yesterdays help on how to get a formula to multiply Baseline Work
* Project Sale Price to return the Baseline Hourly Rate. That works perfectly
now (Thanks, Marc Soester!)

Now, I need to use an Iif function to evaluate a project flag (Canadian or
US dollar contract) and if Canadian, multiply the result by the exchange
factor. Here's the formula I entered:

IIf([Enterprise Project Flag1]="Yes",([Enterprise Project Cost1]/[Baseline
Work]*60)*1.145,([Enterprise Project Cost1]/[Baseline Work])*60)

The dialog box accepts the formula, but the Canadian projects are still
showing the USD hourly rate, even though I have set the flag on those
projects to Yes.

Any ideas?
 
M

Marc Soester

hmmm,
i tested it with task custom fields and it worked. I set up 3 custom fields
Cost field [cost1] = Sales Price
Cost field [cost2] = Dollar per hour ( this is the one with the calculation)
flag [flag1] = Dollar

I generated the formular in the cost field "dollar per hour"

IIf([Flag1]=True,([Cost1]/[Baseline Work]*60)*1.145,([Cost1]/[Baseline
Work])*60)
and it seem to work fine.

Can you let me know if your approach is the same? ( it doesnt matter if you
have project or task custom fields, since they have the same behaviour)

Did you make the changes in the global Template, saved it and then restarted
Project Professional? ( this is necessary, otherwise the change doesnt take
place)

if so, once you opened your project did you recalc the project (with the F9
button)?
and if so do you still have the issue?

I assume that you have a baseline set, otherwise the calculation doesnt work.
I am sure we can get this to work :) Looking forward to your reply

--
Marc Soester
State Manager: EPM


Barb PMO said:
I tried replacing the "Yes" with true, but the results are still the same. I
checked the USD projects to make sure I didn't have it reversed to no avail.

Any other ideas?
--
Barb PMO


Marc Soester said:
Hi Barb PMO,

Project flags dont understand "yes" or "no". You will need to use
true for "yes" or
false for "no".

If you try the following it should work:

IIf([Enterprise Project Flag1]=true,([Enterprise Project Cost1]/[Baseline
Work]*60)*1.145,([Enterprise Project Cost1]/[Baseline Work])*60)

Hope it helps

--
Marc Soester
State Manager: EPM


Barb PMO said:
My company uses Project Pro 2003 and Project Server

Thanks for yesterdays help on how to get a formula to multiply Baseline Work
* Project Sale Price to return the Baseline Hourly Rate. That works perfectly
now (Thanks, Marc Soester!)

Now, I need to use an Iif function to evaluate a project flag (Canadian or
US dollar contract) and if Canadian, multiply the result by the exchange
factor. Here's the formula I entered:

IIf([Enterprise Project Flag1]="Yes",([Enterprise Project Cost1]/[Baseline
Work]*60)*1.145,([Enterprise Project Cost1]/[Baseline Work])*60)

The dialog box accepts the formula, but the Canadian projects are still
showing the USD hourly rate, even though I have set the flag on those
projects to Yes.

Any ideas?
 
B

Barb PMO

Who knew there was such a magical thing as a recalc? I didn’t change a thing
other than pressing F9 and it worked like a charm! I didn’t even know that a
recalc was available – I learn something new every day – especially when it
comes to using Project! hehe

I had thought that closing Project Pro, and reopening the project would
recalc, and when it didn’t I assumed the formula was at fault.

Thanks very much! Marc - you rock.

--
Barb PMO


Marc Soester said:
hmmm,
i tested it with task custom fields and it worked. I set up 3 custom fields
Cost field [cost1] = Sales Price
Cost field [cost2] = Dollar per hour ( this is the one with the calculation)
flag [flag1] = Dollar

I generated the formular in the cost field "dollar per hour"

IIf([Flag1]=True,([Cost1]/[Baseline Work]*60)*1.145,([Cost1]/[Baseline
Work])*60)
and it seem to work fine.

Can you let me know if your approach is the same? ( it doesnt matter if you
have project or task custom fields, since they have the same behaviour)

Did you make the changes in the global Template, saved it and then restarted
Project Professional? ( this is necessary, otherwise the change doesnt take
place)

if so, once you opened your project did you recalc the project (with the F9
button)?
and if so do you still have the issue?

I assume that you have a baseline set, otherwise the calculation doesnt work.
I am sure we can get this to work :) Looking forward to your reply

--
Marc Soester
State Manager: EPM


Barb PMO said:
I tried replacing the "Yes" with true, but the results are still the same. I
checked the USD projects to make sure I didn't have it reversed to no avail.

Any other ideas?
--
Barb PMO


Marc Soester said:
Hi Barb PMO,

Project flags dont understand "yes" or "no". You will need to use
true for "yes" or
false for "no".

If you try the following it should work:

IIf([Enterprise Project Flag1]=true,([Enterprise Project Cost1]/[Baseline
Work]*60)*1.145,([Enterprise Project Cost1]/[Baseline Work])*60)

Hope it helps

--
Marc Soester
State Manager: EPM


:

My company uses Project Pro 2003 and Project Server

Thanks for yesterdays help on how to get a formula to multiply Baseline Work
* Project Sale Price to return the Baseline Hourly Rate. That works perfectly
now (Thanks, Marc Soester!)

Now, I need to use an Iif function to evaluate a project flag (Canadian or
US dollar contract) and if Canadian, multiply the result by the exchange
factor. Here's the formula I entered:

IIf([Enterprise Project Flag1]="Yes",([Enterprise Project Cost1]/[Baseline
Work]*60)*1.145,([Enterprise Project Cost1]/[Baseline Work])*60)

The dialog box accepts the formula, but the Canadian projects are still
showing the USD hourly rate, even though I have set the flag on those
projects to Yes.

Any ideas?
 
M

Marc Soester

Thanks for the compliment BarbPMO, a last part to complete your formular is
to capture if you have not set a Baseline, otherwise your formular will
display an error. You can handle this within your formular: if you try the
following your formular will rock too :)

IIf([Baseline Finish]=ProjDateValue("NA"), "No
Baseline",IIf([Flag1]=True,([Cost1]/[Baseline
Work]*60)*1.145,([Cost1]/[Baseline Work])*60))

This will cover the situation of not having set a baseline ( it will
actually tell the user the no baseline has been set) and it avoids getting
calls, why the calculation has an error :)

Take care, till next time
--
Marc Soester
State Manager: EPM


Barb PMO said:
Who knew there was such a magical thing as a recalc? I didn’t change a thing
other than pressing F9 and it worked like a charm! I didn’t even know that a
recalc was available – I learn something new every day – especially when it
comes to using Project! hehe

I had thought that closing Project Pro, and reopening the project would
recalc, and when it didn’t I assumed the formula was at fault.

Thanks very much! Marc - you rock.

--
Barb PMO


Marc Soester said:
hmmm,
i tested it with task custom fields and it worked. I set up 3 custom fields
Cost field [cost1] = Sales Price
Cost field [cost2] = Dollar per hour ( this is the one with the calculation)
flag [flag1] = Dollar

I generated the formular in the cost field "dollar per hour"

IIf([Flag1]=True,([Cost1]/[Baseline Work]*60)*1.145,([Cost1]/[Baseline
Work])*60)
and it seem to work fine.

Can you let me know if your approach is the same? ( it doesnt matter if you
have project or task custom fields, since they have the same behaviour)

Did you make the changes in the global Template, saved it and then restarted
Project Professional? ( this is necessary, otherwise the change doesnt take
place)

if so, once you opened your project did you recalc the project (with the F9
button)?
and if so do you still have the issue?

I assume that you have a baseline set, otherwise the calculation doesnt work.
I am sure we can get this to work :) Looking forward to your reply

--
Marc Soester
State Manager: EPM


Barb PMO said:
I tried replacing the "Yes" with true, but the results are still the same. I
checked the USD projects to make sure I didn't have it reversed to no avail.

Any other ideas?
--
Barb PMO


:

Hi Barb PMO,

Project flags dont understand "yes" or "no". You will need to use
true for "yes" or
false for "no".

If you try the following it should work:

IIf([Enterprise Project Flag1]=true,([Enterprise Project Cost1]/[Baseline
Work]*60)*1.145,([Enterprise Project Cost1]/[Baseline Work])*60)

Hope it helps

--
Marc Soester
State Manager: EPM


:

My company uses Project Pro 2003 and Project Server

Thanks for yesterdays help on how to get a formula to multiply Baseline Work
* Project Sale Price to return the Baseline Hourly Rate. That works perfectly
now (Thanks, Marc Soester!)

Now, I need to use an Iif function to evaluate a project flag (Canadian or
US dollar contract) and if Canadian, multiply the result by the exchange
factor. Here's the formula I entered:

IIf([Enterprise Project Flag1]="Yes",([Enterprise Project Cost1]/[Baseline
Work]*60)*1.145,([Enterprise Project Cost1]/[Baseline Work])*60)

The dialog box accepts the formula, but the Canadian projects are still
showing the USD hourly rate, even though I have set the flag on those
projects to Yes.

Any ideas?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top