AVG Collect with Referenced sheets

jforbis

I am trying to use intake sheets to manage my regional level of data, where they will pull the basic

information from the "network" intake sheet. that all works well, but I now am trying to get is the average of completion % for each project brought into the region(the the average of my 5 regions).

I thought a AVG(Collect would get the job done, but i am very new with smartsheet. These intake sheets are all identical in structure.

The formula:

=AVG(COLLECT({Northeast % Complete} {Central % Complete} {Southeast % Complete} {Southwest % Complete} {West % Complete}, {Central Project ID}{Northeast Project ID}{Southeast Project ID}{Southwest Project ID}{West Project ID}, [Project ID]@row))

This is what each page would look like:

I just get back #UNPARSEABLE

Any idea what I am doing wrong here? is this the wrong way to go about this maybe

heyjay

Looks like you are missing commas in between references. Here is a similar post that might help.

jforbis

=AVG(COLLECT(

"{Northeast % Complete} {Central % Complete} {Southeast % Complete} {Southwest % Complete} {West % Complete}" I Am referencing these ranges. Each regions % Complete

"{Central Project ID}{Northeast Project ID}{Southeast Project ID}{Southwest Project ID}{West Project ID}" Referencing the Project Id row as criteria

[Project ID]@row)) Finally Project ID row on the Network sheet to determine which Project ID to pull avg % for.

That was my thought process at least, it doesn't work obviously. I have tried with commas in between them all and then I get #INVALID OPERATION.

Jason Tarpinian

@jforbis, I saw this old one was still unanswered and wanted to try something with it. You are getting the error because you are referencing multiple ranges, and COLLECT only works with a single range. If I'm following you correctly, you can still get the result your are looking for by using a couple other functions instead.

=(SUMIFS({Northeast % Complete}, {Northeast Project ID}, [Project ID]@row) + SUMIFS({Central % Complete}, {Central Project ID}, [Project ID]@row)) / (COUNTIFS({Northeast Project ID}], [Project ID]@row) + COUNTIFS({Central Project ID}, [Project ID]@row))

Just expand the formula to include all of your regions. But we basically just take it back to manually calculating the average, instead of using the AVG function. The first part of the formula will use SUMIFS to calculate up all of the % you need based on the ID criteria, then the COUNTIFS part of the formula will divide the total number count. Hopefully this helps!