how do i properly collect an average based on multiple criteria?

I am trying to find an average based on 2 criteria. First, the data pulled should match the "Month Number" and second the data pulled should match "2025"which i have in a cell under my "Month" Column. My formula keeps getting a dived by Zero error. in the first image below you can see my data sheet. im trying to pull the MLD PPMH average based on those two criteria. any ideas where im going wrong?

my current formula:

=AVG(COLLECT({PPMH WEEKLY ENTRY Range 1}, {PPMH WEEKLY ENTRY Range 11}, =[Month Number]@row , {PPMH WEEKLY ENTRY Range 3}, =Month1))

Find more posts tagged with

Accepted answers

Brandon Morales

THANKS PAUL. THIS MAKES SENSE- AND NOW IT FINALLY WORKS! THANKS FOR ALL YOUR HELP!

Paul Newcome

Yep. Sorry about that. I was solving fort a similar problem at the same time and got it mixed up. Glad you were able to get it sorted.

All comments

Paul Newcome

I assume that {Range 11} in the second sheet is supposed to be referencing the gray month column in the first sheet. If that assumption is correct, double check that {Range 11} is definitely referencing that column.

If you look closely at the first screenshot (source data), you will see small black arrows in all of the cells in the Year column. This indicates that they are being referenced either by a cross sheet reference or a cell link. I am guessing here it is {Range 3}.

If you then look at your Month column, you will see that these arrows are not present. This means the Month column is not being referenced from an external source such as a {Cross Sheet Reference}.

Having said that… You can just reference the Date column without the need for the month or year columns like so:

=AVG(COLLECT({Source Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = [Month Number]@row, IFERROR(YEAR(@cell), 0) = Month1)))

Brandon Morales

hey Paul, im kind of lost with this… in the formula you provided, im using the source sheet to collect the data to be averaged but i dont see anywhere that you are using the source sheets date column?

Is there no way to make the average collect func

tion work? Im a bit lost with the iferror function…

anyway, this is the new formula i tried but i get incorrect argument.

Paul Newcome

My apologies. My fingers got ahead of me.

=AVG(COLLECT({Source Sheet ColumnTo Average}, {Source Sheet Date Column}, AND(IFERROR(MONTH(@cell ), 0) = [Month Number]@row , IFERROR(YEAR(@cell ), 0) = Month1)))

Brandon Morales

I still get divide by zero…

=AVG(COLLECT({PPMH WEEKLY ENTRY Range 1}, {PPMH WEEKLY ENTRY Range 2}, AND(IFERROR(MONTH(@cell ), 0) = [Month Number]@row , IFERROR(YEAR(@cell ), 0) = Month1)))

Paul Newcome

Ok. Is {Range 2} definitely referencing the Date column? Is that column on the source sheet set as a date type column? Exactly how are those dates populated?

Brandon Morales

Yes, range 2 is the date column and it set as date column type.

The date is populated by looking at the week number and converting to date

Paul Newcome

How exactly is the date populated though? It looks like a column formula that also has some cross sheet references.

Brandon Morales

the week is entered through a form and then i have a separate sheet that converts a week to a date. i use index to get the date from that sheet

Paul Newcome

Ok. And what is the formula that converts the week to a date, and is THAT column also set as a date type column?

Brandon Morales

the first date in the picture below is manually populated and then the rest use the date above and add 7. this too, is a date formatted column.

Paul Newcome

Hmm… How are the month numbers and the year in the metrics sheet being populated?

Brandon Morales

Using the Year and Month functions.

Brandon Morales

i got it to work!

my data sheet was getting zero error because those months have no entries yet!

Brandon Morales

one more follow up question, how would my formula look if i wanted the average based on the month number and wheter or not the date is in the last 365 days?

Brandon Morales

this is the formula i tried but its not valid:

=AVG(COLLECT({PPMH WEEKLY ENTRY_MLD PPMH}, {PPMH WEEKLY ENTRY_Date}, AND(@cell > TODAY(-365), MONTH(@cell ) = [Month Number]@row )))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.