Metrics Pulling Help

Hi there! I have a submission sheet and a metrics sheet. I am attempting to create a formula on the metrics sheet pulling from the submission sheet. I have no trouble pulling counts, but averages are giving me some trouble.. I am able to pull the average based on 1 criteria, but no more than that.

I am looking to average the effort of submissions by 2 criteria- the line number, and if the submission was in the last 7 days.

For example: I have a submission in the last 7 days, for line 2, and the effort was 5. I have a second submission in the last 7 days, for line 2, and the effort was 8. I want my formula to calculate the average of these two submissions and to make sure it does not count any submissions outside of the 7 days or desired line.

Any help would be greatly appreciated!

Find more posts tagged with

Sheets

Formulas

Sheet Summary

metrics

Accepted answers

All comments

Darren Mullen

@ccousineau311 You'll have to write your own average calculation.

Use countifs() to count the rows with your mutiple criteria then divide the countifs() by the told number of records.

ccousineau311

Thank you- However, I'm unsure if this is the solution.. I do not want to average the number of submissions but rather the effort grading within each submission. Perhaps I'm simply not understanding your suggestion..

Paul Newcome

You would use an AVG/COLLECT combination.

=AVG(COLLECT({Range to average}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria))

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.