Cross Reference Formula To Search by Division and Calculate Average % Complete

GraceK
GraceK
edited 12/07/23 in Formulas and Functions

I have a project plan that includes work for multiple divisions, and I am looking to utilize a formula on a different sheet to cross reference the project plan and calculate the average % complete for all tasks that are assigned to a given division. Bonus if we can specify only summary tasks or tasks without children.

I'm starting with =INDEX(COLLECT({% Complete}, {Assigned SCD}, "SMPH"), 1) but cannot figure out:

  1. How to have it look for ALL % Complete on tasks assigned to SMPH, not just a specific row or column
  2. How to input an Average function

Thanks!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!