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.