# Calculating Avg Workdays w/ Conditions

Options
✭✭✭✭

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'd be grateful for any insight! Thank you in advance. :)

Tags:

• ✭✭✭✭✭✭
Options

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"))

• ✭✭✭✭
Options

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. :(

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!