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.
Thanks so much in advance for your help!
Wendy
Answers

Hi @Wendy Young,
Couple of questions 
 what cell do you have your formula in?
 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
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

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.
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 64 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives