Average value by month

Gaurav Chauhan

Hi Team,

I have a scenario wherein I am trying to pull the AVG score by month wherein few cells for each of those months would be blank. I have tried AVERAGEIF with <>0 and AVG(Collect( ... but i keep getting invalid or other error types.

Can you help.

markkrebs

use Avg( )

Gaurav Chauhan

Can you please write the entire formulae based on month criteria

Gaurav Chauhan

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

Can you validate.

Genevieve P.

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

Gaurav Chauhan

@Genevieve P. Thank you

My formulae is returning an error - Invalid operation

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

Can you please identify what am I missing. Thanks.

Genevieve P.

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

Gaurav Chauhan

@Genevieve P. Thanks

The re-arranged formulae is populating the avg. value however its giving the same output for all months.Strange.

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

Genevieve P.

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?

Gaurav Chauhan

@Genevieve P. Thanks. This is giving Divide by zero error. please validate my formuale.

Genevieve P.

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)?

Gaurav Chauhan

@Genevieve P. - I am pasting the snippet for the below. All cells in these two fields don't have a value. These are the only two I entered as dummy data to build and Test the formulae.

Genevieve P.

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?