Combining AVG/COLLECT/NETWORKDAYS

The Shew

I am attempting to calculate the average number of working days it takes to complete a request, Criteria I want to base the data collection on include:

1. urgency level
2. Month and Year(I created a rolling month chart to see trends-see below screenshot)
   1. The Work Completion Date determines the month it will be reported on
   2. This is where things get tricky. The Work Completion and Start Date Columns provide the full date format, rather than MO/YEAR format. That works for calculating the networking days to complete work, but it does not work for finding the average in a given month. So, I have to pull month and year from the date in order to see if it meets the criteria.

All calculations are being done in a separate metric sheet

I am doing my best to not add additional columns to the source sheet. There are several entries on this sheet, and I want to prevent exceeding max cells for as long as possible. So, combining calculations, without the use of a helper column, would be ideal.

Here is the calculation I put together. I'm coming up with an #UNPARSEABLE error. Please review and let me know where the error may exist. Thank you.

=AVG(COLLECT(NETWORKDAYS({Work Start Date}, {Work Completion Date}),{Work Completion Date},MONTH({Work Completion Date}) = [Rolling Date Month]65, {Work Completion Date},YEAR({Work Completion Date}) = [Rolling Date Year]65, {Urgency Level}, "Low")))

DKazatsky2

Hi @The Shew,

The "NETWORKDAYS" function is expecting a start and end date but you are passing it ranges, I suspect this is causing your parsing errors.

I know you are trying to avoid the addition of helper columns, but in this scenario, it may be necessary. If you calculate the NETWORKDAYS in the source, you can then COLLECT those values using your criteria.

Hope this helps,

Dave

Jason Tarpinian

Looks like you're trying to build an array formula (that's at least what it's called in Excel), which Smartsheet doesn't support . COLLECT() is looking for a range as the first input, but NETWORKDAYS() can only return you a value, not a range. Unfortunately, as far as I know, you would need a helper column. Could you maybe make a helper sheet if you are concerned about cell limits? Just do an =INDEX({Source Column 1}, [Row #]@row) for each column you need (so your helper sheet would only have 4-5 columns, then do you calcs off of that?

DKazatsky2

Hi @The Shew,

The "NETWORKDAYS" function is expecting a start and end date but you are passing it ranges, I suspect this is causing your parsing errors.

I know you are trying to avoid the addition of helper columns, but in this scenario, it may be necessary. If you calculate the NETWORKDAYS in the source, you can then COLLECT those values using your criteria.

Hope this helps,

Dave

Jason Tarpinian

Looks like you're trying to build an array formula (that's at least what it's called in Excel), which Smartsheet doesn't support . COLLECT() is looking for a range as the first input, but NETWORKDAYS() can only return you a value, not a range. Unfortunately, as far as I know, you would need a helper column. Could you maybe make a helper sheet if you are concerned about cell limits? Just do an =INDEX({Source Column 1}, [Row #]@row) for each column you need (so your helper sheet would only have 4-5 columns, then do you calcs off of that?

The Shew

That is helpful. I had a feeling that was the issue. Thank you both for the guidance.

BTW, @Jason Tarpinian, Great pro tip with the INDEX in the helper sheet. I can use that in many other instances.