How do I sum multiple rows of different colors

[Mark]

I have set up the column to be r=color RED, g =color GREEN, y = color YELLOW.
Basically Red = no started, Y = working, and Green = completed. I want to
sum all the field to determine percentage complete. So if all Green then
100% complete. If I have 10 fields and 5 are Green and 5 are either yellow
or red then total is 50%. How do I create that formula?

 

[JackD]

I'd drop the r, y, g and use numbers 0, 0.5, 1. The indicator for the column
can work just as well with numerical values and the formula becomes
(assuming you are using text fields for the values)

([Text1] + [Text2] + ...[Text10]) * 10 & "%"

 

[JackD]

Oh, part two is summing them. You can set the summary task (in the customize
fields dialog box) to show the average or sum or use the formula. You could
have the various text fields sum and then your calculated field can use the
formula.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..

I'd drop the r, y, g and use numbers 0, 0.5, 1. The indicator for the column
can work just as well with numerical values and the formula becomes
(assuming you are using text fields for the values)

([Text1] + [Text2] + ...[Text10]) * 10 & "%"

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
.

I have set up the column to be r=color RED, g =color GREEN, y = color YELLOW.
Basically Red = no started, Y = working, and Green = completed. I want to
sum all the field to determine percentage complete. So if all Green then
100% complete. If I have 10 fields and 5 are Green and 5 are either yellow
or red then total is 50%. How do I create that formula?

 

[Brian K - Project MVP]

I have set up the column to be r=color RED, g =color GREEN, y = color
YELLOW. Basically Red = no started, Y = working, and Green =
completed. I want to sum all the field to determine percentage
complete. So if all Green then 100% complete. If I have 10 fields
and 5 are Green and 5 are either yellow or red then total is 50%.
How do I create that formula?

While Jack has you started on the how I will get you thinking about
why? There are some holes in your theory that summing these colors will
give you some useful info. If yellow means it is just 'in work' then
why would you want yellow to equal 50% complete when it could be 5% or
95%. This measure that Jack helps you come up with will be wildly
inaccurate. It has only a tiny chance of every being right. Is this
measure a good idea?

 

[JackD]

Brian,

I agree with you somewhat. You could get almost the same information with
just 0 and 100% measures.
But I disagree with you too. Knowing a task (or some sort of checklist item)
is in progress is important so the advantages of this approach vs. 0/100 may
be greater than just the "accuracy" of the sum.

 

[Brian K - Project MVP]

Brian,

I agree with you somewhat. You could get almost the same information
with just 0 and 100% measures.
But I disagree with you too. Knowing a task (or some sort of
checklist item) is in progress is important so the advantages of this
approach vs. 0/100 may be greater than just the "accuracy" of the sum.

Sure but then dont call it 50% complete. Call it "In Progress". It
seemed as if his request would have made a summary task with 5 complete
tasks and 5 tasks that were either not started or in progress 50%
complete. This is just wrong data even if all the 'yellow' tasks were
99.9% complete the summary would only be close to 50% complete if all
the tasks were the same 'size'. If all the complete tasks were 1 day
and the 'yellow' or 'red' tasks were 20 days then it gets even worse.
The other side of the same bad coin is true if the complete tasks were
really long and the incomplete ones were short. It would say 50%
complete when really it was almost 100% complete.

My fear with this is that someone (an exec or upper manager) will not
get that these numbers are approximations. I am against using what
appears to most people as an exact measure (50%, 75%, etc) for
something that is behind the scenes only a very rough and inaccurate
measure.

 

[Steve House [Project MVP]]

But it looks like the poster is mixing up % Complete with % Physical
Complete. He says he's setting up 10 fields and if 5 of them are marked
done he wants it to show 50% complete. That sounds like a checklist of some
sort and he's calling 50% done having half of the items checked off. That
completely ignores any indication of task duration or the length of time
that was required to complete those checkpoints.. I suggest that while it
might be nice to know how many of the items have been completed, it really
provides no useful information whatsoever for managing the project or its
work schedule. IMHO, the crucial question is not how much of the task is
done but rather when will it be finished.
--
Steve House [MVP]
MS Project Trainer & Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

Brian,

I agree with you somewhat. You could get almost the same information with
just 0 and 100% measures.
But I disagree with you too. Knowing a task (or some sort of checklist
item)
is in progress is important so the advantages of this approach vs. 0/100
may
be greater than just the "accuracy" of the sum.

 

[JackD]

Steve,

I'm going to disagree with you here. Sometimes it is important to know
whether the checklist items are completed. They may be a measure of quality.
Knowing across the project which category of checklist items are NOT being
completed is a valuable indicator. Granted, it doesn't give you much
information about the TIME dimension of the schedule, but it is useful
information nonetheless.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..

But it looks like the poster is mixing up % Complete with % Physical
Complete. He says he's setting up 10 fields and if 5 of them are marked
done he wants it to show 50% complete. That sounds like a checklist of some
sort and he's calling 50% done having half of the items checked off. That
completely ignores any indication of task duration or the length of time
that was required to complete those checkpoints.. I suggest that while it
might be nice to know how many of the items have been completed, it really
provides no useful information whatsoever for managing the project or its
work schedule. IMHO, the crucial question is not how much of the task is
done but rather when will it be finished.

 

[Steve House [Project MVP]]

I don't doubt it's useful in some situations, just saying that Project is a
less than optimal way of tracking such checklists and that one should be
very careful not to confuse things by thinking it's a measure of "%
Complete" the same as you see in Project's tracking data when it is actually
not even remotely related to it.
--
Steve House [MVP]
MS Project Trainer & Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

Steve,

I'm going to disagree with you here. Sometimes it is important to know
whether the checklist items are completed. They may be a measure of
quality.
Knowing across the project which category of checklist items are NOT being
completed is a valuable indicator. Granted, it doesn't give you much
information about the TIME dimension of the schedule, but it is useful
information nonetheless.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
.
"Steve House [Project MVP]" <[email protected]>
wrote
in message news:OKblZ%[email protected]...