Use of formulas

[Athena]

Hello,

I want to display the money left in the project on the left of task as a cost field. The very first task at the top has the money (B) initially allocated for the project. The next down has some X amont less (B-X) , and the second has some X+Y amount less (B-X-Y) etc. until all the money is spent. I created two columns 1. Budget 2. Expense. I enter expenses for every task in the Expense column. I want to the budget column to show : Money Left New = Budget (B) - MoneyLeft Old by . The value of the Budget is set in the cell for the first task. Then the othetr cells in the Budget column should show "Budget (B) - the sum of expenses in the cell(s) of the Expense column. How can I doo that?

Task Name Duration Start Finish Budget Expense

Close Term Sheet 27d Thu 6/26/08 Fri 8/1/08 "$250,000.00" "0.00"

Drafting CAD 32d Thu 6/26/08 Fri 8/8/08 "$246,945.00" "3,055.00"

Pay President's salary 131d? Wed 7/2/08 Wed 12/31/08 "$206,945.00" "40,000.00"

......


Thank you,

Athena

 

[Jan De Messemaeker]

Hi,

Any formula that uses data from an other task cannot be handled through custom fields, you need a VBA procedure.

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf
Hello,

I want to display the money left in the project on the left of task as a cost field. The very first task at the top has the money (B) initially allocated for the project. The next down has some X amont less (B-X) , and the second has some X+Y amount less (B-X-Y) etc. until all the money is spent. I created two columns 1. Budget 2. Expense. I enter expenses for every task in the Expense column. I want to the budget column to show : Money Left New = Budget (B) - MoneyLeft Old by . The value of the Budget is set in the cell for the first task. Then the othetr cells in the Budget column should show "Budget (B) - the sum of expenses in the cell(s) of the Expense column. How can I doo that?

Task Name Duration Start Finish Budget Expense

Close Term Sheet 27d Thu 6/26/08 Fri 8/1/08 "$250,000.00" "0.00"

Drafting CAD 32d Thu 6/26/08 Fri 8/8/08 "$246,945.00" "3,055.00"

Pay President's salary 131d? Wed 7/2/08 Wed 12/31/08 "$206,945.00" "40,000.00"

.....


Thank you,

Athena

 

[JulieS]

Hi Athena,

Its a bit tough to tell what your information looks like (things don't line
up really well) but let's see if I can help.

I assume you are doing this in Microsoft Project not Microsoft Excel.
Assuming you have entered data into the following fields:

Budget -- you've entered into Cost1 field
Expense -- you've entered into Cost2 field
Add Cost3 -- renamed it "Remaining Budget"

Customize the field (right click on the column header and choose Customize
from the shortcut menu). Click the Formula button and enter:
[Cost1]-[Cost2]

Click OK to close the formula dialog and return to the Customize dialog box.
In the section "Calculation for task and group summary rows" select "Use
Formula" if you've entered information in the Cost1 and Cost2 fields for
Budget and Expenses. If you haven't entered summary data, you'll want to
Rollup Cost1 and Cost2 fields.

I hope this helps.

Julie

 

[Athena]

Hello Julie,

Thank you very much. Your solution works to some degree.
Here is the explanation with example:

For task 1: Budget=250,000.00 Expense=0.0 Remaining Budget = 250,000.00
For task 2: Budget=250,000.00 Expense=3,055.00 Remaining Budget = 246,945.00
For task 3: Budget=246.945.00 Expense=40,000.00 Remaining Budget = 206,945.00
.....
i.e. new budget = old budget - sum of expenses so far. So the formula should be if it were in Excel: G2 = E2 - F1,
G3 = E3 - F2, ....

Here is the table (If you view the email in html format you will be able to see the table)

A E F G
1 Task Name Budget Expense Remaining Budget

2 Close Term Sheet "$250,000.00" "0.00" "250,000.00"

3 Drafting CAD "$250,000.00" "3,055.00" "$246,945.00"

4 Pay President's salary "$246,945.00" "40,000.00" "$206,945.00"

Thank you again,

Athena



----- Original Message -----
From: "JulieS" <[email protected]>
Newsgroups: microsoft.public.project
Sent: Wednesday, August 20, 2008 1:04 PM
Subject: RE: Use of formulas

Hi Athena,

Its a bit tough to tell what your information looks like (things don't line
up really well) but let's see if I can help.

I assume you are doing this in Microsoft Project not Microsoft Excel.
Assuming you have entered data into the following fields:

Budget -- you've entered into Cost1 field
Expense -- you've entered into Cost2 field
Add Cost3 -- renamed it "Remaining Budget"

Customize the field (right click on the column header and choose Customize
from the shortcut menu). Click the Formula button and enter:
[Cost1]-[Cost2]

Click OK to close the formula dialog and return to the Customize dialog box.
In the section "Calculation for task and group summary rows" select "Use
Formula" if you've entered information in the Cost1 and Cost2 fields for
Budget and Expenses. If you haven't entered summary data, you'll want to
Rollup Cost1 and Cost2 fields.

I hope this helps.

Julie

Hello,

I want to display the money left in the project on the left of task as
a cost field. The very first task at the top has the money (B) initially
allocated for the project. The next down has some X amont less (B-X) , and
the second has some X+Y amount less (B-X-Y) etc. until all the money is
spent. I created two columns 1. Budget 2. Expense. I enter expenses for
every task in the Expense column. I want to the budget column to show :
Money Left New = Budget (B) - MoneyLeft Old by . The value of the Budget
is set in the cell for the first task. Then the othetr cells in the Budget
column should show "Budget (B) - the sum of expenses in the cell(s) of the
Expense column. How can I doo that?

Task Name Duration Start Finish
Budget Expense

Close Term Sheet 27d Thu 6/26/08 Fri 8/1/08
"$250,000.00" "0.00"

Drafting CAD 32d Thu 6/26/08 Fri 8/8/08
"$246,945.00" "3,055.00"

Pay President's salary 131d? Wed 7/2/08 Wed 12/31/08
"$206,945.00" "40,000.00"

.....


Thank you,

Athena

 

[JulieS]

Hi Athena,

Thanks very much for the additional information. I understand better
what you are trying to do and, as Jan noted, in order to reference
data from Task 1 on Task 2, it would require some VBA. Project is
very good about referring to the same task date (same row), but custom
formulas such as I gave are not suited for a running sum type scenario
that you need.

Try posting to the developer newsgroup
(microsoft.public.project.developer) for some guidance on the VBA
option if you're interested.

I hope this helps.

Julie
Hello Julie,

Thank you very much. Your solution works to some degree.
Here is the explanation with example:

For task 1: Budget=250,000.00 Expense=0.0
Remaining Budget = 250,000.00
For task 2: Budget=250,000.00 Expense=3,055.00 Remaining
Budget = 246,945.00
For task 3: Budget=246.945.00 Expense=40,000.00 Remaining
Budget = 206,945.00
.....
i.e. new budget = old budget - sum of expenses so far. So the
formula should be if it were in Excel: G2 = E2 - F1,
G3 = E3 - F2, ....

Here is the table (If you view the email in html format you will
be able to see the table)

A E F
G
1 Task Name Budget Expense
Remaining Budget

2 Close Term Sheet "$250,000.00" "0.00"
"250,000.00"

3 Drafting CAD "$250,000.00" "3,055.00"
"$246,945.00"

4 Pay President's salary "$246,945.00" "40,000.00"
"$206,945.00"

Thank you again,

Athena



----- Original Message -----
From: "JulieS" <[email protected]>
Newsgroups: microsoft.public.project
Sent: Wednesday, August 20, 2008 1:04 PM
Subject: RE: Use of formulas

Hi Athena,

Its a bit tough to tell what your information looks like (things
don't line
up really well) but let's see if I can help.

I assume you are doing this in Microsoft Project not Microsoft
Excel.
Assuming you have entered data into the following fields:

Budget -- you've entered into Cost1 field
Expense -- you've entered into Cost2 field
Add Cost3 -- renamed it "Remaining Budget"

Customize the field (right click on the column header and choose
Customize
from the shortcut menu). Click the Formula button and enter:
[Cost1]-[Cost2]

Click OK to close the formula dialog and return to the Customize
dialog box.
In the section "Calculation for task and group summary rows" select
"Use
Formula" if you've entered information in the Cost1 and Cost2 fields
for
Budget and Expenses. If you haven't entered summary data, you'll
want to
Rollup Cost1 and Cost2 fields.

I hope this helps.

Julie

Hello,

I want to display the money left in the project on the left of
task as
a cost field. The very first task at the top has the money (B)
initially
allocated for the project. The next down has some X amont less
(B-X) , and
the second has some X+Y amount less (B-X-Y) etc. until all the
money is
spent. I created two columns 1. Budget 2. Expense. I enter expenses
for
every task in the Expense column. I want to the budget column to
show :
Money Left New = Budget (B) - MoneyLeft Old by . The value of the
Budget
is set in the cell for the first task. Then the othetr cells in the
Budget
column should show "Budget (B) - the sum of expenses in the cell(s)
of the
Expense column. How can I doo that?

Task Name Duration Start
Finish
Budget Expense

Close Term Sheet 27d Thu 6/26/08 Fri
8/1/08
"$250,000.00" "0.00"

Drafting CAD 32d Thu 6/26/08 Fri
8/8/08
"$246,945.00" "3,055.00"

Pay President's salary 131d? Wed 7/2/08 Wed
12/31/08
"$206,945.00" "40,000.00"

.....


Thank you,

Athena