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?
Answers
-
How is the source data that comes in from the VLOOKUP populated?
-
Does this help?
-
I mean the data that the VLOOKUP is pulling in. The source data for that function.
-
The rest of the columns are VIN number, purchase date, etc.
-
Which of those is feeding the [OGS Program Area] column?
-
Reported Organization, the match is on the vehicle ID.
-
I didn't see it the first time, but in your screenshot of the VLOOKUP formula there is a cell containing the #NO MATCH error. This error will push through to all formulas referencing it. Try using an IFERROR wrapped around the VLOOKUP to get rid of that and see if it fixes your AVG/COLLECT.
-
I went to the first column and edited the vlookup to wrap the iferror function around it. It worked successfully as row 4 shows.
I am still getting the #NO MATCH problem in the Idle Minutes column.
Here is a look at that formula:
The vehicle 131233 (row 5) should absolutely match.
I did wrap the IFERROR in the OGS Program area section and that worked really well, but it didn’t fix the overall problem.
For the formula above, here’s a look at that reference:
-
PS. I did try to wrap the iferror around the idle minutes column, but it still gave me all the no matches, even with the better text.
PPS. Thank you for taking a look at this--I'm almost thinking this looks like a defect...but before I say that officially, I want to make sure it's not just user error. :-)
-
My suggestion at this point would be to go through the entire source sheet and make sure everything is being wrapped in IFERRORs just to clean all data up.
-
Paul, I cannot thank you enough. So, I fixed it, BUT I had to do more than just wrap all my formulas with the "IFERROR"--although, without doing that first, I would have never considered my ultimate solution. I also had to add a helper column that took the vehicle ID and converted it to a value first--using the "VALUE" function. Again, thank you for working with me. ~L
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!