Average formula for cell values calculated from another formula

Imran Virk

I'm using the SS Jira connector for Jira and have create a template for my team to use.  As part of this template I want to calculate the % completed field based on the task's status (the built in % complete of the gantt project only work at the parent level after manually inputing a value for the child task).  

 

I've been able to set a formula for all of my child tasks where the percentage complete changes based on the status column for that row.   This part works well.  Here's the formula for reference:

 

=IF(Status9 = "Backlog", "0%", IF(Status9 = "In Progress", "14%", IF(Status9 = "Code Review", "29%", IF(Status9 = "QA Dev/UAT1", "42%", IF(Status9 = "QA Live", "58%", IF(Status9 = "Client Review", "71%", IF(Status9 = "Next Release", "86%", IF(Status9 = "Done", "100%")))))))) 

 

However, I am trying to now get an average of the child tasks at each parent level, and then ultimately an average at the epic level which averages the values of all the parent tasks.  No matter which variations of the AVG formula I've tried it does not return a value.  In fact, even if do a simple SUM that too returns a 0.  Can anyone help with a forumla that would calculate the average of the children tasks?  

 

Here's an overview of the task hierarchy:

 

-          EPIC:  Average of all level 1 (i.e. parent) tasks

o   Parent 1:  Average of all child tasks

§  Child 1:  formula resulting in a percentage

§  Child 2: formula resulting in a percentage

§  Child 3: formula resulting in a percentage

o   Parent 2: Average of all child tasks

§  Child 1:  formula resulting in a percentage

§  Child 2: formula resulting in a percentage

§  Child 3: formula resulting in a percentage

§  Child 4:  formula resulting in a percentage

§  Child 5: formula resulting in a percentage

§  Child 6: formula resulting in a percentage

§  Child 7:  formula resulting in a percentage

§  Child 8: formula resulting in a percentage

§  Child 9: formula resulting in a percentage

o   Parent 3: Average of all child tasks

§  Child 1:  formula resulting in a percentage

§  Child 2: formula resulting in a percentage

§  Child 3: formula resulting in a percentage

o   Parent 4:  Average of all child tasks

§  Child 1:  formula resulting in a percentage

o   Parent 5: Average of all child tasks

§  Child 1:  formula resulting in a percentage

§  Child 2: formula resulting in a percentage

§  Child 3: formula resulting in a percentage

§  Child 4:  formula resulting in a percentage

§  Child 5: formula resulting in a percentage

o   Parent 6: Average of all child tasks

§  Child 1:  formula resulting in a percentage

§  Child 2: formula resulting in a percentage

§  Child 3: formula resulting in a percentage

§  Child 4:  formula resulting in a percentage

§  Child 5: formula resulting in a percentage

§  Child 6: formula resulting in a percentage

o   Parent 7: Average of all child tasks

§  Child 1:  formula resulting in a percentage

§  Child 2: formula resulting in a percentage

§  Child 3: formula resulting in a percentage

§  Child 4:  formula resulting in a percentage

 

§  Child 5: formula resulting in a percentage

Zack S

Hello Imran, 

 

Thank you for contacting us. The reason the AVG and SUM formulas aren’t working is because of how your formula is written, but it is an easy fix. As your original formula is written, you are telling the formula to place a text value of 29%, or 42% as a result. Including the % sign and quotes in the formula tells the result to be text, which then can’t be averaged or summed. 

 

If you adjust the original formula, and replace the percentage results with their decimal equivalent (50% being .5), you will then be able to run an AVG on those results. Here is an example of how the first several statements would look from your formula: =IF(Status9 = "Backlog", 0, IF(Status9 = "In Progress", .14, IF(Status9 = "Code Review", .29)

 

You’ll see that I changed the value to a decimal value and also removed the quotes around the percentage result. Making these changes to the entire formula will cause the formula to return numbered results instead, and you can then run averages in the parent rows. 

 

Once you’ve done this, you’ll want to select the entire % Complete column by clicking the column header, and then click the % icon in the toolbar, and that will allow the column to still show the results in a similar percentage format (rather than a decimal).  

 

Please let me know if you have any questions on this process. 

 

-Zack