I have an Excel spreadsheet in which I'll be tracking progress for numerous
tasks (rows 1-5) along a set of milestones (cols A-E). I need a formula to
handle some adding here.

Each col is worth an equal percentage of the overall task's completion. Some
tasks, however, won't hit every milestone due to being slightly different in
nature from the default task type. I need to measure each task's percentage
of completion using the milestones as the unit of measurement.

As an example:

Task #1 will hit milestones A, B, C, D, & E, in order. Each milestone (A-E)
will have to be worth 20% of the overall progression of Task #1. Task #1 hits
all five milestones, and will eventually reach 100% completion.

Task #2 will progress through A, B, D, & E (but will never hit C). Each
milestone (A, B, D, & E) will have to be worth 25% of the overall progression
of Task #2. Since Task #2 hits all four of the four milestones intended for
it to hit, Task #2 will still need to eventually reach 100% completion.

I need a formula that will show % completion for each Task, but it needs to
be dynamic enough that I can use the same formula for Task #1 & Task #2.

What I was thinking was to use a cell within each Task's row to mark if that
Task will hit a specific milestone column. Other users will fill those
columns in. I would like Excel to calculate along the lines of "if Task X is
identified as hitting (some amount of) milestones, that amount of milestones
is used to calculate the overall % completion (instead of just the total
number of available milestones in the spreadsheet).

Does this make sense? Any clues for proceeding?

Thanks,
Jeff

Let's assume column F contains the count of how many milestones will be hit.
(Either have user input this, or some use of the COUNTA function.) Columns
A:E are your various milestones, where you place an "X" or something to
signify milestone is met.

Formula then is:
=COUNTA(A2:E2)/F2
Format cell as percentage.

Other idea is if you use F:J to check which milestones will be hit/included,
formula becomes:
=COUNTA(A2:E2)/COUNTA(F2:J2)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jeff Wow" wrote:

I have an Excel spreadsheet in which I'll be tracking progress for numerous
tasks (rows 1-5) along a set of milestones (cols A-E). I need a formula to
handle some adding here.

Each col is worth an equal percentage of the overall task's completion. Some
tasks, however, won't hit every milestone due to being slightly different in
nature from the default task type. I need to measure each task's percentage
of completion using the milestones as the unit of measurement.

As an example:

Task #1 will hit milestones A, B, C, D, & E, in order. Each milestone (A-E)
will have to be worth 20% of the overall progression of Task #1. Task #1 hits
all five milestones, and will eventually reach 100% completion.

Task #2 will progress through A, B, D, & E (but will never hit C). Each
milestone (A, B, D, & E) will have to be worth 25% of the overall progression
of Task #2. Since Task #2 hits all four of the four milestones intended for
it to hit, Task #2 will still need to eventually reach 100% completion.

I need a formula that will show % completion for each Task, but it needs to
be dynamic enough that I can use the same formula for Task #1 & Task #2.

What I was thinking was to use a cell within each Task's row to mark if that
Task will hit a specific milestone column. Other users will fill those
columns in. I would like Excel to calculate along the lines of "if Task X is
identified as hitting (some amount of) milestones, that amount of milestones
is used to calculate the overall % completion (instead of just the total
number of available milestones in the spreadsheet).

Does this make sense? Any clues for proceeding?

Thanks,
Jeff

Excel 2007 Table
http://www.mediafire.com/file/214dzd...04_06_09a.xlsx