Divide by Zero Error With AVG(COLLECT(...

Partcheasy

Hoping someone can help because I am totally twisted with this one. I don't use AVG(COLLECT very often so I may be missing something simple here… What I'm trying to do is take the average of Column17 where both the year (Column22) and the weekday (Column9) match the year and weekday that I've indicated in Column21. (Yes I am going to rename all of these columns to something more helpful, just wanted to workshop this a bit first).

I am able to average Column17 no problem with the standard AVG function, so there doesn't appear to be any problem data there, and both column9 and column22 have no errors or anything that I think would cause an issue. Any help would be greatly appreciated!

Also, FYI there are many instances of the data in Column17 further down not being blank, and also matching the criteria I'm asking it for.

The error that I am getting which you can see is #DIVIDE BY ZERO

Formula below, but also inserted here:

=AVG(COLLECT($[Column17]$1:$[Column17]$2497, $[Column9]$1:$[Column9]$2497, [Column21]@row, $[Column22]$1:$[Column22]$2497, $[Column21]$8))

Paul Newcome

Looks like [Column22] is outputting the years as text strings, but your formula is looking for a number value. How are you populating [Column22]?

Paul Newcome

Looks like [Column22] is outputting the years as text strings, but your formula is looking for a number value. How are you populating [Column22]?

Partcheasy

Aha, that's the issue, thanks Paul! You've got a good eye. I was using the RIGHT() function to take the year out of Column8, but I missed that it was coming out left-aligned.

Paul Newcome

Happy to help. 👍️