Averaging a month and year of data

Elizabeth Aird

I want to average the month of April, 2022. I can't figure out the month, much less the year. I did look this up but I can't seem to get it to work.

=AVG(COLLECT(Boiler Gas Usage}:{Boiler Gas Usage}, {Date}:{Date}, IFERROR(MONTH(@CELL),0)=4))

ericncarr

There's actually an AVERAGEIF function you could use to do that.

https://help.smartsheet.com/function/averageif

So your formula would be:

=AVERAGEIF(Date:Date, AND(MONTH(@cell) = 4, YEAR(@cell) = 2022), [Boiler Gas Usage Daily]:[Boiler Gas Usage Daily])

This tells it to check the Date column for any values whose month equals 4 AND year equals 2022, then gets the average of the Boiler Gas Usage Daily values for all corresponding rows.

Personally, if this was a running thing that I wanted to automate, one of the steps I'd do is create a month and year column (=month(Date@row) for example) that extracts the month and year from the Date column and use that to make a column formula like this:

=AVERAGEIF(Date:Date, AND(MONTH(@cell) = Month@row, YEAR(@cell) = Year@row), [Boiler Gas Usage Daily]:[Boiler Gas Usage Daily])

Then it would be really easy to do lookups from another sheet if you need to.

ericncarr

There's actually an AVERAGEIF function you could use to do that.

https://help.smartsheet.com/function/averageif

So your formula would be:

=AVERAGEIF(Date:Date, AND(MONTH(@cell) = 4, YEAR(@cell) = 2022), [Boiler Gas Usage Daily]:[Boiler Gas Usage Daily])

This tells it to check the Date column for any values whose month equals 4 AND year equals 2022, then gets the average of the Boiler Gas Usage Daily values for all corresponding rows.

Personally, if this was a running thing that I wanted to automate, one of the steps I'd do is create a month and year column (=month(Date@row) for example) that extracts the month and year from the Date column and use that to make a column formula like this:

=AVERAGEIF(Date:Date, AND(MONTH(@cell) = Month@row, YEAR(@cell) = Year@row), [Boiler Gas Usage Daily]:[Boiler Gas Usage Daily])

Then it would be really easy to do lookups from another sheet if you need to.

sshariati

Need help with this formula but to calculate for several months in a year. I've tried various syntax's to try including month 1-12 (Jan-Dec) but keep getting errors. i.e. how to calculate average of Boiler Gas Usage Daily for January, Feb, March, April etc..

=AVERAGEIF(Date:Date, AND(MONTH(@cell) = 4, YEAR(@cell) = 2022), [Boiler Gas Usage Daily]:[Boiler Gas Usage Daily])

sshariati

I've tried smthg like this: =AVERAGEIF([Incident Start Time]:[Incident Start Time], AND(MONTH(@cell) = 5, YEAR(@cell) = 2021), [Incident Duration]:[Incident Duration]), AVERAGEIF([Incident Start Time]:[Incident Start Time], AND(MONTH(@cell)=6,YEAR(@cell)=2021), [Incident Duration]:[Incident Duration])

Genevieve P.

Hi @sshariati

As soon as you have more than 1 criteria, you'll need to use an AVG(COLLECT combination. The Collect filters down your data so the AVG knows what to average.

Here's the structure:

=AVG(COLLECT([Column to Average]:[Column to Average], [Criteria Column]:[Criteria Column], "Criteria 1", [Criteria Column]:[Criteria Column], "Criteria 2"))

So in your case, something like:

=AVG(COLLECT([Boiler Gas Usage Daily]:[Boiler Gas Usage Daily], Date:Date, MONTH(@cell) = 4, Date:Date, YEAR(@cell) = 2022))

If you're still receiving an error, Date functions sometimes error if they have text in the column or blank cells. We can get around this by using an IFERROR function:

=AVG(COLLECT([Boiler Gas Usage Daily]:[Boiler Gas Usage Daily], Date:Date, IFERROR(MONTH(@cell), 0) = 4, Date:Date, IFERROR(YEAR(@cell),0) = 2022))

Cheers,

Genevieve

sshariati

Thank you so much! But this example formula is for one month only, my trouble is how to add for months January through December (this is how my data is, not based off of the example at the top where it's showing only for april (4)) do I add a comma and add the following months??

=AVG(COLLECT([Boiler Gas Usage Daily]:[Boiler Gas Usage Daily], Date:Date, MONTH(@cell) = 4, Date:Date, YEAR(@cell) = 2022, AVG(COLLECT([Boiler Gas Usage Daily]:[Boiler Gas Usage Daily], Date:Date, MONTH(@cell) = 5, Date:Date, YEAR(@cell) = 2022)))

4= April

5= May

etc.. for June, July..

My exact scenario is this:

I tried:

=AVG(COLLECT([Incident Duration]:[Incident Duration], Month:Month, MONTH(@cell) = 1, Year:Year, YEAR(@cell) = 2024, AVG(COLLECT([Incident Duration]:[Incident Duration], Month:Month, MONTH(@cell) = 2, Year:Year, YEAR(@cell) = 2024)))

Month and Year columns are formulas that convert the Incident Start Time to separate the month and year. I added Month 2 because there is more data to come for February and so on..

However I'm still seeing #unparsable

Genevieve P.

Hi @sshariati

If I'm understanding you correctly, you first want to find the average of each month separately, then a total, overall average based on those averages, is that right?

Personally, I would set up each separate AVG formula in different cells, then reference those cells in a total AVG formula.

However if you want to do it in one long formula, you would have each separate AVG(COLLECT()) < closed off, then a comma, and have them inside an AVG function:

=AVG(formula for April, formula for May, formula for June) and so on. Does that make sense?

=AVG(AVG(COLLECT([Boiler Gas Usage Daily]:[Boiler Gas Usage Daily], Date:Date, IFERROR(MONTH(@cell), 0) = 4, Date:Date, IFERROR(YEAR(@cell),0) = 2022)), AVG(COLLECT([Boiler Gas Usage Daily]:[Boiler Gas Usage Daily], Date:Date, IFERROR(MONTH(@cell), 0) = 5, Date:Date, IFERROR(YEAR(@cell),0) = 2022)))

Cheers,

Genevieve

sshariati

Thank you could you help me customize for my fields instead?

I've tried the following:

=AVG(COLLECT([Incident Duration]:[Incident Duration], Month:Month, MONTH(@cell) = 1, Year:Year, YEAR(@cell) = 2024, AVG(COLLECT([Incident Duration]:[Incident Duration], Month:Month, MONTH(@cell) = 2, Year:Year, YEAR(@cell) = 2024)))

but showing unparsable

I've also tried this:

=AVG(COLLECT([Incident Duration]:[Incident Duration], Month:Month, "June", Year:Year, "2021"))

and this

=AVG(COLLECT([Incident Duration]:[Incident Duration], Month:Month, "June", Year:Year, "2021", =AVG(COLLECT([Incident Duration]:[Incident Duration], Month:Month, "July", Year:Year, "2021")))

and still doesn't work

I  only want to find the average of each month per year (i.e. 2023, 2024 etc) separately using one long formula I can use down an entire column called "Average Duration" that will satisfy all months and relevant years that appear. Average Duration would calculate average value per month/year that are listed under Month and Year and using corresponding values under "incident duration"

If you suggest outputting values separely, is there a way to display on same page without interfering with the used columns? I'd like to display at the top of the same smartsheet where values are also located. How would I associate value with the correct text in month? It'll only output a value but it would be nice if i.e. January = 1234

sshariati

I'm thinking another option, can you provide guidance on how I can use averageif statement referencng values in another smartsheet? I'd like to pull in values from another sheet to calculate average duration in another sheet.

Bascially use this statement that works in the same sheet: =AVERAGEIF([Incident Start Time]:[Incident Start Time], AND(MONTH(@cell) = 5, YEAR(@cell) = 2021), [Incident Duration]:[Incident Duration])

but i want to use this formula in another sheet and reference the values elsewhere

Genevieve P.

Hi @sshariati

If we go with your second option, we would use {cross sheet references} instead of [in sheet] references, but the structure would be the same! See: Create cross sheet references

=AVERAGEIF([Incident Start Time]:[Incident Start Time], AND(MONTH(@cell) = 5, YEAR(@cell) = 2021), [Incident Duration]:[Incident Duration])

Would turn into:

=AVERAGEIF({Incident Start Time column reference}, AND(MONTH(@cell) = 5, YEAR(@cell) = 2021), {Incident Duration Column Reference})

If this works for you, I would suggest going down that route instead of the other formulas above - let me know what you would prefer doing. If you'd rather keep the formulas in the same sheet, it would be helpful to see a screen capture of your sheet but please block out sensitive data.

Cheers,

Genevieve

sshariati

This works! Thank you so much!