# Issue with Progress calculation of weighted values

✭✭✭

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.

Wendy

• ✭✭✭✭✭

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

Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

Feel free to email me: ramzi@cedartreeconsulting.com

• ✭✭✭

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

• ✭✭✭

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.

• ✭✭✭✭✭✭

Are you using hierarchies?

• ✭✭✭

THANK YOU!!

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

• ✭✭✭✭✭✭

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]))

• ✭✭✭

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. . .

• ✭✭✭✭✭✭

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

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

• ✭✭✭

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.

• ✭✭✭✭✭✭

How are your percentages being entered?

• ✭✭✭

Manually entering them.

• ✭✭✭

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.

• ✭✭✭✭✭✭

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?

• ✭✭✭

The column is formatted as a percentage.