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

  • markkrebs
    markkrebs ✭✭✭✭✭✭
  • Gaurav Chauhan
    Gaurav Chauhan ✭✭✭✭✭✭

    Can you please write the entire formulae based on month criteria

  • Gaurav Chauhan
    Gaurav Chauhan ✭✭✭✭✭✭

    =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

  • Gaurav Chauhan
    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.

  • 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

  • Gaurav Chauhan
    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" <> ""))

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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Gaurav Chauhan
    Gaurav Chauhan ✭✭✭✭✭✭
    edited 12/08/23

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


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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Gaurav Chauhan
    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.


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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!