Help with formula please

[Caroline Kerridge]

Hello

I have two fields:

Enterprise Project Cost1 (Budgeted Revenue) = [Baseline Cost]

Enterprise Project Test 13 (% Revenue Recognised) =

cstr(cint(IIf(IsNull([Baseline Cost]),"No Baseline",IIf(IsNull([Actual
Cost]),1,[Actual Cost])*100/IIf(IsNull([Cost]),1,[Cost])))) & "%"

The second one is a percentage but has been converted to a string from
currency I think (this was done by someone else before me)

Now I want to calculate:

Revenue Recognised to Date = Enterprise Project Cost1 x Enterprise Project
Test 13

e.g. 5,000 = 10,000 x 50%

I have run out of Project Cost fields so I am entering it as a number and
have tried the following formula but keep getting errors...

Enterprise Project Number 4 = (IIf(IsNull([Baseline Cost]),"No
Baseline",IIf(IsNull([Actual Cost]),1,[Actual
Cost])/IIf(IsNull([Cost],1,[Cost]))))*[Enterprise Project Cost1]

Can anyone help?

 

[Dale Howard [MVP]]

Caroline --

I have simulated your situation and have come up with a solution that works
very well. First of all, replace your existing formula in the Enterprise
Project Text 13 field (% Revenue Recognised) with the following formula:

IIf([Baseline Cost]=0,"No Baseline",cint(([Actual Cost]/[Cost])*100) & "%")

My formula is much simpler that your current formula. The formula works
correctly whether a Baseline has been saved or not. Second, use a custom
enterprise Project Text field (such as Enterprise Project Text 14) as your
Revenue Reconized to Date field, and in this field use the following
formula:

IIf([Baseline Cost]=0,"No Baseline",Format(([Actual
Cost]/[Cost])*[Enterprise Project Cost1],"$#,##0.00"))

The formula will format the number using the familiar Currency formatting
that you see in a Cost field. After you edit the first field and add the
second field, then save and close your Enterprise Global file, and then exit
and relaunch Microsoft Project Professional. Open any project and
immediately press the F9 function key to recalculate the project. You MUST
do this with every current project to recalculate the project and to
calculate the new formulas. Then click Project - Project Information and
you should see each of the three custom fields, calculated and displayed
correctly. Hope this helps.

 

[Caroline Kerridge]

Dale

Thank you so much - this works perfectly!
--
Caroline

Caroline --

I have simulated your situation and have come up with a solution that works
very well. First of all, replace your existing formula in the Enterprise
Project Text 13 field (% Revenue Recognised) with the following formula:

IIf([Baseline Cost]=0,"No Baseline",cint(([Actual Cost]/[Cost])*100) & "%")

My formula is much simpler that your current formula. The formula works
correctly whether a Baseline has been saved or not. Second, use a custom
enterprise Project Text field (such as Enterprise Project Text 14) as your
Revenue Reconized to Date field, and in this field use the following
formula:

IIf([Baseline Cost]=0,"No Baseline",Format(([Actual
Cost]/[Cost])*[Enterprise Project Cost1],"$#,##0.00"))

The formula will format the number using the familiar Currency formatting
that you see in a Cost field. After you edit the first field and add the
second field, then save and close your Enterprise Global file, and then exit
and relaunch Microsoft Project Professional. Open any project and
immediately press the F9 function key to recalculate the project. You MUST
do this with every current project to recalculate the project and to
calculate the new formulas. Then click Project - Project Information and
you should see each of the three custom fields, calculated and displayed
correctly. Hope this helps.

Hello

I have two fields:

Enterprise Project Cost1 (Budgeted Revenue) = [Baseline Cost]

Enterprise Project Test 13 (% Revenue Recognised) =

cstr(cint(IIf(IsNull([Baseline Cost]),"No Baseline",IIf(IsNull([Actual
Cost]),1,[Actual Cost])*100/IIf(IsNull([Cost]),1,[Cost])))) & "%"

The second one is a percentage but has been converted to a string from
currency I think (this was done by someone else before me)

Now I want to calculate:

Revenue Recognised to Date = Enterprise Project Cost1 x Enterprise
Project
Test 13

e.g. 5,000 = 10,000 x 50%

I have run out of Project Cost fields so I am entering it as a number and
have tried the following formula but keep getting errors...

Enterprise Project Number 4 = (IIf(IsNull([Baseline Cost]),"No
Baseline",IIf(IsNull([Actual Cost]),1,[Actual
Cost])/IIf(IsNull([Cost],1,[Cost]))))*[Enterprise Project Cost1]

Can anyone help?

 

[Dale Howard [MVP]]

Caroline --

You are more than welcome, my friend!

Dale

Thank you so much - this works perfectly!
--
Caroline

Caroline --

I have simulated your situation and have come up with a solution that
works
very well. First of all, replace your existing formula in the Enterprise
Project Text 13 field (% Revenue Recognised) with the following formula:

IIf([Baseline Cost]=0,"No Baseline",cint(([Actual Cost]/[Cost])*100) &
"%")

My formula is much simpler that your current formula. The formula works
correctly whether a Baseline has been saved or not. Second, use a custom
enterprise Project Text field (such as Enterprise Project Text 14) as
your
Revenue Reconized to Date field, and in this field use the following
formula:

IIf([Baseline Cost]=0,"No Baseline",Format(([Actual
Cost]/[Cost])*[Enterprise Project Cost1],"$#,##0.00"))

The formula will format the number using the familiar Currency formatting
that you see in a Cost field. After you edit the first field and add the
second field, then save and close your Enterprise Global file, and then
exit
and relaunch Microsoft Project Professional. Open any project and
immediately press the F9 function key to recalculate the project. You
MUST
do this with every current project to recalculate the project and to
calculate the new formulas. Then click Project - Project Information and
you should see each of the three custom fields, calculated and displayed
correctly. Hope this helps.

Hello

I have two fields:

Enterprise Project Cost1 (Budgeted Revenue) = [Baseline Cost]

Enterprise Project Test 13 (% Revenue Recognised) =

cstr(cint(IIf(IsNull([Baseline Cost]),"No Baseline",IIf(IsNull([Actual
Cost]),1,[Actual Cost])*100/IIf(IsNull([Cost]),1,[Cost])))) & "%"

The second one is a percentage but has been converted to a string from
currency I think (this was done by someone else before me)

Now I want to calculate:

Revenue Recognised to Date = Enterprise Project Cost1 x Enterprise
Project
Test 13

e.g. 5,000 = 10,000 x 50%

I have run out of Project Cost fields so I am entering it as a number
and
have tried the following formula but keep getting errors...

Enterprise Project Number 4 = (IIf(IsNull([Baseline Cost]),"No
Baseline",IIf(IsNull([Actual Cost]),1,[Actual
Cost])/IIf(IsNull([Cost],1,[Cost]))))*[Enterprise Project Cost1]

Can anyone help?