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

ilene_healy68056
ilene_healy68056 ✭✭✭✭✭
in Formulas and Functions

Hi

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


· Share on FacebookShare on Twitter

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @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.

    image.png


    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! 👍️

    10xViz.com

    · Share on FacebookShare on Twitter

Answers

  • ilene_healy68056
    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!!

    · Share on FacebookShare on Twitter
  • ilene_healy68056
    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

    · Share on FacebookShare on Twitter
  • Paul Newcome
    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.

    10xViz.com

    · Share on FacebookShare on Twitter
  • ilene_healy68056
    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

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

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

    Please post again if you come across any other questions

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @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.

    image.png


    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! 👍️

    10xViz.com

    · Share on FacebookShare on Twitter
  • ilene_healy68056
    ilene_healy68056 ✭✭✭✭✭

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

    :-)

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!