Calculating Avg Workdays w/ Conditions

Hello!

I'm currently trying to calculate the average amount of workdays it takes for each person to complete a specific request on a metrics sheet from a parent sheet. For example: How long does it take me on average to complete a FOIA request? How long does it take me on average complete a litigation request?

Request Type = Type of Request (In this instance, FOIA)

AVG WDAYS = Column from parent sheet that has a formula calculating the average workdays for each specific line item to be completed

PLU Assigned = Person assigned to this request

=IFERROR((AVG({AVG WDAYS} = "", "", IF({Request Type}, CONTAINS("FOIA", @cell), {PLU Assigned}, HAS(@cell, "Bridgett Fenner")))))

I've made several adjustments since this morning, and moved around parentheses.

I'd be grateful for any insight! Thank you in advance. :)

Find more posts tagged with

Formulas

Accepted answers

All comments

Christian G.

Your "if" statement is not correctly stated.

Use a Collect to agglomerate data that you want to calculate.

=AVG(Collect([what you want to average], Critera range1, Critera1, criteria range 2, criteria2, ... etc

=AVG(COLLECT({AVG WDAY},{Request Type},="FIOA",{PLU Assigned},="Bridgett Fenner"))

fennerb

The COLLECT function! That makes complete sense since I've never heard of it.

=AVG(COLLECT({AVG WDAYS}, {Request Type}, ="FOIA", {PLU Assigned}, ="Bridgett Fenner"))

I'm closer, but I'm still getting INCORRECT ARGUMENT. :(

Christian G.

does your arguments in brackets {} are columns from the same sheet?

fennerb

That is correct. They're all from the same sheet.

L_123

Make sure each of the range references have the same number of cells in them.

Christian G.

To make sure the collect function does what you want, use the JOIN function and look at the result in a cell

=Join(collect(...),char(10))

The char(10) is a carriage return and it make it easier to read if you set the cell's text to wrap.

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.