Average, Collect are not working when referencing a formula outcome
Hi, I'm running into an issue. I have a column of values [OGS Program Area] that are populated from a vlookup. I need to average the Program Area score based on that output. My formula is below.
=AVG(COLLECT(Score:Score, [OGS Program Area]:[OGS Program Area], "DRE ADMINISTRATION"))
I keep getting the #NO MATCH error. I feel like my error stems from a problem where my AVG formula cannot read the end-state vlookup value. Any workaround I have tried does not work. If I manually create a column and type in the cell DRE ADMINISTRATION, then the calculation works seamlessly. I do not want to have to manually type thousands of these values each month.
Can anyone help with this issue?
Help Article Resources
Check out the Formula Handbook template!