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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@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" <> ""))
-
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?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@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)?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@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?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!