Avg(collect) across multiple columns, for specific criteria = unparseable

I am trying to get an overall average % complete for all locations for their "Adoption" phase.

While trying to debug it, I got an average of everything to work for my "Average Score" summary field. I used this formula:

=AVG(COLLECT([NA Hub]1:[Haarlem MSDIS]140, [NA Hub]1:[Haarlem MSDIS]140, ISNUMBER(@cell)))

Next, I am trying to build on that to add in the "TC1 Stage Gate" equal to "Adoption" and get that into the "Adoption Average Score" summary field. But I have run into an #unparseable error for the new formula that I can't figure out.

=AVG(COLLECT([NA Hub]1:[Haarlem MSDIS]140, ISNUMBER(@row),[TC1 Stage Gate]:[TC1 Stage Gate],"Adoption"))

Any advice?

2020-02-19 17_32_43-HUBs Assessment Summary - Smartsheet.com.png

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @ilene_healy68056

You're asking the formula to both look at the row and at the entire range.

In order to only Average the rows that have Adoption, but then take the average across all 5 columns, you will need to first calculate the average per-column & row, then average all 5 of those together.

For example, this would be the average just for the NA Hub column:

AVG(COLLECT([NA Hub]:[NA Hub], [NA Hub]:[NA Hub], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption"))

You'll notice that you first have to list the range to Average (NA Hub column), then the two ranges and their two criteria (that NA Hub contains a number and that TC1 Stage Gate says "Adoption")

You will need to build this formula for each column:

NA Hub:

AVG(COLLECT([NA Hub]:[NA Hub], [NA Hub]:[NA Hub], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption"))

LA Hub:

AVG(COLLECT([LA Hub]:[LA Hub], [LA Hub]:[LA Hub], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption"))

AP Hub:

AVG(COLLECT([AP Hub]:[AP Hub], [AP Hub]:[AP Hub], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption"))

EMEA Hub (Lucerne):

AVG(COLLECT([EMEA Hub (Lucerne)]:[EMEA Hub (Lucerne)], [EMEA Hub (Lucerne)]:[EMEA Hub (Lucerne)], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption"))

Haarlem MSDIS:

AVG(COLLECT([Haarlem MSDIS]:[Haarlem MSDIS], [Haarlem MSDIS]:[Haarlem MSDIS], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption"))

Now you can add all these together, and wrap one large "AVG" around the whole thing. Try this:

=AVG(AVG(COLLECT([NA Hub]:[NA Hub], [NA Hub]:[NA Hub], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption")), AVG(COLLECT([LA Hub]:[LA Hub], [LA Hub]:[LA Hub], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption")), AVG(COLLECT([AP Hub]:[AP Hub], [AP Hub]:[AP Hub], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption")), AVG(COLLECT([EMEA Hub (Lucerne)]:[EMEA Hub (Lucerne)], [EMEA Hub (Lucerne)]:[EMEA Hub (Lucerne)], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption")), AVG(COLLECT([Haarlem MSDIS]:[Haarlem MSDIS], [Haarlem MSDIS]:[Haarlem MSDIS], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption")))

In all honesty, there may be a simpler way to consolidate this information, since it's a lot of repetition... but this is the way I can think of at the moment since you have two criteria ("Adoption" and a Number).

Let me know if this works for you!

Cheers,

Genevieve

Paul Newcome

One thing you could do differently would be to average out each of the columns individually in their own fields/cells, then use field/cell references in place of the formulas in the overall AVG formula, but that still requires individual formulas for each percentage column.

Another option would be to create a new "helper column" for each number column. These helper columns would replicate [TC1 Stage Gate] in your case.

This would provide ranges of the same size for the AVG.

So for example, if you have the columns

Percentage1..........Percentage2..........Percentage3..........Status

and you wanted to average all percentage columns based on the Status column, you would need to add in two additional status columns so the range sizes would match (of course you can hide these extra columns to keep the sheet looking clean).

Percentage1..........Percentage2..........Percentage3..........Status..........Status2..........Status3

In the [Status2] and [Status3] columns, you would use a basic cell reference to replicate the data in the Status column

=$Status@row

(NOTE: Using the "$" to lock in the column name means you can dragfill over into the next column as well as down for faster replication of the formula)

Then you would only need one AVG/COLLECT along the lines of

=AVG(COLLECT([Percentage1]:[Percentage3], [Percentage1]:[Percentage3], ISNUMBER(@cell), Status:[Status3], "In Progress"))

Paul Newcome

@ilene_healy68056 Happy to help! 👍️

As a pointer... Since you are already calculating the totals in the Sheet Summary fields, you may be able to get away with a single helper column where each row simply replicates the data in the different Sheet Summary fields. Then you could use cell linking or cross sheet references pointed at a single cell in this "Reference" column.

Here is an example. In the below image, I wouldn't actually be using the Notes column. I only included that column here so that you could see how I actually populated the reference column.

This makes it so that I would only need one helper column [Summary Reference Column] instead of a bunch of them, and I can use either cell linking or cross sheet references to pull that data onto a metrics sheet.

Looking at the overall possibilities, I think that (assuming you are able) using the Sheet Summary fields for this particular case is actually the better way to go. Great thinking! 👍️

All comments

Genevieve P.

Hi @ilene_healy68056

You're asking the formula to both look at the row and at the entire range.

For example, this would be the average just for the NA Hub column:

AVG(COLLECT([NA Hub]:[NA Hub], [NA Hub]:[NA Hub], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption"))

You'll notice that you first have to list the range to Average (NA Hub column), then the two ranges and their two criteria (that NA Hub contains a number and that TC1 Stage Gate says "Adoption")

You will need to build this formula for each column:

NA Hub:

AVG(COLLECT([NA Hub]:[NA Hub], [NA Hub]:[NA Hub], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption"))

LA Hub:

AVG(COLLECT([LA Hub]:[LA Hub], [LA Hub]:[LA Hub], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption"))

AP Hub:

AVG(COLLECT([AP Hub]:[AP Hub], [AP Hub]:[AP Hub], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption"))

EMEA Hub (Lucerne):

AVG(COLLECT([EMEA Hub (Lucerne)]:[EMEA Hub (Lucerne)], [EMEA Hub (Lucerne)]:[EMEA Hub (Lucerne)], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption"))

Haarlem MSDIS:

AVG(COLLECT([Haarlem MSDIS]:[Haarlem MSDIS], [Haarlem MSDIS]:[Haarlem MSDIS], ISNUMBER(@cell), [TC1 Stage Gate]:[TC1 Stage Gate], "Adoption"))

Now you can add all these together, and wrap one large "AVG" around the whole thing. Try this:

Let me know if this works for you!

Cheers,

Genevieve

ilene_healy68056

Thank you so much for your response. This is so helpful, I really appreciate it. I see i was trying to 'shortcut things' and your clarification makes total sense. I will give this a try today. Thanks!!

Paul Newcome

Another option would be to create a new "helper column" for each number column. These helper columns would replicate [TC1 Stage Gate] in your case.

This would provide ranges of the same size for the AVG.

So for example, if you have the columns

Percentage1..........Percentage2..........Percentage3..........Status

Percentage1..........Percentage2..........Percentage3..........Status..........Status2..........Status3

In the [Status2] and [Status3] columns, you would use a basic cell reference to replicate the data in the Status column

=$Status@row

(NOTE: Using the "$" to lock in the column name means you can dragfill over into the next column as well as down for faster replication of the formula)

Then you would only need one AVG/COLLECT along the lines of

=AVG(COLLECT([Percentage1]:[Percentage3], [Percentage1]:[Percentage3], ISNUMBER(@cell), Status:[Status3], "In Progress"))

ilene_healy68056

Hi Paul, thanks for your feedback. You guys are awesome!

I did start to add the column Hub Averages for Adoption as separate fields on the sheet summary. Do you think helper columns would be the better way to go? And then, to get the grand average of all Adoption, it seems to me I should be able to then average my new Hub averages fields in the sheet summary, because they are each already doing the math and filtering for number+Adoption. Should this work or am I still being to simplistic and missing it? I normally do use Helper columns but was trying out the Sheet Summary functionality.....which one would be the best?

for the "Overall Adoption Avg":

=AVG([NA Hub Adoption Avg]#, [LA Hub Adoption Avg]#, [AP Hub Adoption Avg]#, [EMEA Hub Adoption Avg]#, [Haarlem MSDIS Adoption Avg]#)

2020-02-20 09_47_09-HUBs Assessment Summary - Smartsheet.com.png

Thanks

Ilene

Paul Newcome

If you are going to use the Sheet Summary, I would say that you are probably going about it the most efficient way that I can think of.

You can also format those fields to show as a percentage as well.

The only catch is that Sheet Summary fields cannot be used in cross sheet references or cross sheet cell linking, so you would need to move that data onto the sheet itself if you were planning on using it that way.

Metrics widgets on a dashboard can show Sheet Summary fields, but I believe that is the only type of widget that can directly reference those fields (other than displaying the entire sheet in a Web Content widget.

ilene_healy68056

Thank you for your feedback on my approach. I think this will help me with my immediate need but I will start building in the Helper Columns so that this data can be referenced in other sheets for the longer term.

Thank you Paul and Genevieve for your help. I appreciate your time and help very much!

Ilene

Genevieve P.

No problem! Glad that we could sort something out :)

Please post again if you come across any other questions

Paul Newcome

@ilene_healy68056 Happy to help! 👍️

Here is an example. In the below image, I wouldn't actually be using the Notes column. I only included that column here so that you could see how I actually populated the reference column.

Looking at the overall possibilities, I think that (assuming you are able) using the Sheet Summary fields for this particular case is actually the better way to go. Great thinking! 👍️

ilene_healy68056

I like that approach, thank you so much! I think that will work well for me.

:-)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.