Hey @Gaurav Chauhan

If you're skipping blank cells, you'll want to skip "" instead of 0. There also looks to be an extra closing parentheses in there... try this:

=AVG(COLLECT({Scope}, {Scope}, <> "", {Month}, "Jan"))

Notice that Scope is listed twice. The first time is to list the range you want to average, the second time is to identify that this range has criteria you're trying to filter by (not blank).

Let me know if this worked for you!

Cheers,

Genevieve

Hey @Gaurav Chauhan

Looks like you're just missing listing the range twice: once to AVG, once to Filter:

=AVG(COLLECT({Project Tracker - 2024 Range Value score}, {Project Tracker - 2024 Range Value score}, <>"", {Project Tracker - 2024 Month}, "Jan"))

Cheers!

Genevieve

Hi @Gaurav Chauhan

It looks like you removed a range and added criteria at the end - can you try copy/pasting my formula above? You need the Value reference twice, duplicated.

Here's a simple version:

=AVG(COLLECT({Value}, {Value}, <>"", {Month}, "Jan"))

This is how an AVG(COLLECT works:

=AVG(COLLECT({Column to Average}, {Column with criteria}, "Criteria"))

However the column that you have with criteria is the same as the column you want to average. This means you need to list it again:

=AVG(COLLECT({Project Tracker - 2024 Range Value score}, {Project Tracker - 2024 Range Value score}, <>"", {Project Tracker - 2024 Month}, "Jan"))

Does this make sense?

Hi @Gaurav Chauhan

This error occurs if there's no data to Average after the filter of the "Month" has been applied. Can you post a screen capture of the source sheet with the data to average? Is it possible these values are seen as text (appearing on the left side of the cell) instead of numerical (appearing on the right side of the cell)?

Hi @Gaurav Chauhan

Since you only entered data into 2 rows that have "May", all other months will provide you with a Divide By 0 error since there's no data. You can wrap this in an iferror to product something else instead:

=IFERROR(formula, "")

However I do see that your one MAY row has the same error, when in your case it should have a value. How are you getting the "Value Score" in this sheet? Are you typing in 9.0, or is it a formula?

If it's a formula, make sure there are no quotes around any of your numbers. Can you post the formula here?