Average, Collect are not working when referencing a formula outcome

LeanneV

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?

Paul Newcome

How is the source data that comes in from the VLOOKUP populated?

LeanneV

Does this help?

Paul Newcome

I mean the data that the VLOOKUP is pulling in. The source data for that function.

LeanneV

The rest of the columns are VIN number, purchase date, etc.

Paul Newcome

Which of those is feeding the [OGS Program Area] column?

LeanneV

Reported Organization, the match is on the vehicle ID.

Paul Newcome

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.

LeanneV

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:

LeanneV

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. :-)

Paul Newcome

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.

LeanneV

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