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

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?
Best Answers
-
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
Need more information? π | Help and Learning Center
γγγ«γ‘γ― (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!π | Global Discussions
-
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"))
-
@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! ποΈ
Answers
-
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
Need more information? π | Help and Learning Center
γγγ«γ‘γ― (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!π | Global Discussions
-
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!!
-
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"))
-
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]#)
Thanks
Ilene
-
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.
-
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
-
No problem! Glad that we could sort something out :)
Please post again if you come across any other questions
Need more information? π | Help and Learning Center
γγγ«γ‘γ― (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!π | Global Discussions
-
@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! ποΈ
-
I like that approach, thank you so much! I think that will work well for me.
:-)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!