Average value by month
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.
Answers

use Avg( )

Can you please write the entire formulae based on month criteria

=AVG(COLLECT({Scope},<>0), {Month}, "Jan"))
Can you validate.

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

@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.

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

@Genevieve P. Thanks
The rearranged 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" <> ""))

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?

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

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

@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.

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?
Help Article Resources
Categories
Check out the Formula Handbook template!