Issue with Progress calculation of weighted values

Wendy Young

Good morning,

I'm calculating the completion progress for a project with weighted values on individual tasks. However, it's only calculating 50% of the actual progress.

This is the formula that I'm using: =AVGW(PROGRESS:PROGRESS, [WEIGHTED VALUE]:[WEIGHTED VALUE])

Here's a screenshot of my sheet:

Please show me what I'm doing wrong.

Thanks so much in advance for your help!

Wendy

Ramzi K

Hi @Wendy Young,

Couple of questions -

1. what cell do you have your formula in?
2. What value are you getting and what value are you expecting? I just want to make sure I understand what you mean by "it's only calculating 50% of the actual progress"

Cheers,

Ramzi

Wendy Young

Hi Ramzi,

My formula is in the PROGRESS 1 cell.

With regard to the expected value, I'm expecting the progress value to show the true progress in accordance with the weighted value for each task. As you can see from the screen shot, that even when I set the progress value for each individual weighted task at 100%, the PROGRESS 1 value is only showing at 50% complete instead of 100% complete. Does that make sense?

Using the same progress task values, if I copy and paste this same AVGW formula into a separate blank column, it still only calculates at 75%.

If I use the AVG formula instead of the AVGW, I get 67%. Then if I copy and paste the AVG formula into a separate blank column, it calculates at 65%.

Please advise as to how I can get an accurate weighted progress average.

Thank you!

Wendy

Wendy Young

Good morning. Can you please get back to me on this? My team has 22 project sheets with this same issue. Thank you in advance for prioritizing.

Paul Newcome

Are you using hierarchies?

Wendy Young

THANK YOU!!

Yes, but not entering any values in the child rows.

Paul Newcome

Try using a CHILDREN reference, so it ONLY pulls the darker blue rows instead of pulling the entire column.

=AVGW(CHILDREN(PROGRESS@row), CHILDREN([WEIGHTED VALUE]))

Wendy Young

It gave me the #UNPARSEABLE error. Since my initial question submission here, I changed "WEIGHTED VALUE" to "WEIGHT". I made the change in the formula you provided above, but it's still giving me the error. Here's the formula I plugged in: =AVGW(CHILDREN(PROGRESS@row), CHILDREN(WEIGHT))

Screen shot of the table. . .

Paul Newcome

My apologies. I forgot to tack on the @row reference for the Weight column...

=AVGW(CHILDREN(PROGRESS@row), CHILDREN(WEIGHT@row))

Wendy Young

I tried that earlier when it gave me the unparseable error, then it gave me the #DIVIDE BY ZERO error. I just copied and pasted your formula here and it still gave me the divide by zero error.

Paul Newcome

How are your percentages being entered?

Wendy Young

Manually entering them.

Wendy Young

I don't know if this will make a difference or not, but the "WEIGHT" value that is being used to calculate the weighted progress value that we're trying to get to, is a =SUM(WEIGHT:WEIGHT) formula.

Paul Newcome

When you are manually entering... Do you enter a number and the column is formatted as a percentage, or are you manually entering the % as well?

Wendy Young

The column is formatted as a percentage.