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.

The Shew

@Jason Tarpinian, I am curious about the [Row #]@row function. Will you please provide more information? I tried it within the INDEX formula without luck. I don't have an automated row ID# and cannot go in daily to add rows to the helper sheet.

Thank you.

Jason Tarpinian

Row # is a helper column I use quite a bit, it's a workaround to reference the current row number, since there isn't a native way to do so. If you have your "Auto-Number" column, just reference it as I do Row ID in the formula below. Then no matter how you sort or rearrange your rows, "Row #" will always be the row # of your sheet.

=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)