nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Average Resolution with Date

Cayla Davis

I have my average resolution for each ticket that is opened, and then i want to reach a summary sheet for each quarter that gives me my average resolution based on the quarter by month - i'm using my resolved date column so its in a date format. This is the formula that I am using, however i keep getting errors

This gives me #DIVIDE BY ZERO and then i add an IFERROR, and then i get #UNPARSEABLE

=AVG(COLLECT({ResolutionDuration}, {ResolutionDuration}, <>"", {ResolvedDate}, IFERROR(MONTH(@cell ), 0) >= 1, {ResolvedDate}, IFERROR(MONTH(@cell ), 0) <= 3, {ResolvedDate}, IFERROR(YEAR(@cell ), 0) = 2025))

Does anyone know what i'm doing wrong?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

The final argument is still missing. Give this a try:

=IFERROR(AVG(COLLECT({ResolutionDuration}, {ResolvedDate})), IFERROR(MONTH(@cell ), 0) >= 1, {ResolvedDate}, IFERROR(MONTH(@cell ), 0) <= 3, {ResolvedDate}, IFERROR(YEAR(@cell ), 0) = 2025)), "output for main formula error")

All comments

Philip Robbins

Hi @Cayla Davis, I've not generally tried adding IFERROR functions within a COLLECT function - that could be what's leading to the UNPARSEABLE error. If an AVG/COLLECT formula is returning DIVIDE BY ZERO this implies it's not pulling back any values that meet the COLLECT criteria. This could be a valid response depending on the criteria you are setting, but if you want to handle it, I would suggest wrapping the entire formula in an IFERROR function.

Consider also wrapping the MONTH and YEAR functions in a VALUE function to ensure it's not pushing a string into a comparison. I've found I need to do this more often than not with date-related criteria.

Paul Newcome

You can use the IFERROR inside of the COLLECT function. I do this very frequently especially when trying to evaluate month and year from a date.

There is actually no syntax issue that should be causing the #UNPARSEABLE error based on the formula in your post.

Are you able to provide a screenshot of the formula open in the sheet as if you are about to edit it?

Shaz

@Cayla Davis your formula is correct, but you cant use it here. problem with data range.

If date format and content are correct and consitance 1. Empty rows below the data is causing MONTH(@cell) to return errort also 2. Any unresolved tickets cause error. You can verify this by putting a month formula for the date range and delete empty rows. Formula works until you reload sheet then returns error.

You first iferror doesnt meets the condition >=1 by retuning 0 so it ignores collect criteria. But second iferror does 0 less than 3 but collect criteria fails because it tries to find date with 0 as condition. You might have got #DIVIDED error only when 1st iferror was tested.

Simple solution is to find quarter in the new column for every resolved date and average it or add skipper logic to month formula if resolution date is not a date and remove iferror

However I want to tell you, Finding average resolution duration in ticket management system is not a right metric to watch.

You have to show either of 1. Tier based average on complexity of the ticket , 2. SLA based scoring or 3. Use MEDIAN() if you stuck with one metric.

Yulichka

Why not have an IFEROR Column Formula to capture the Received Date and Completed Date and then AVG the Column to measure the entire year and then break them down into Ranges in Sheet Summary to measure the months? Much simpler and faster, but not as pretty.

Paul Newcome

@Shaz The syntax of the IFERROR wrapped around the MONTH and YEAR functions is the correct way to do this. I do this very frequently. Wrapping the MONTH and YEAR functions in their own IFERROR statements allows you to essentially skip rows that do not have dates and include rows that do.

Paul Newcome

@Yulichka This does not meet the need for calculating at the quarterly level.

Yulichka

I missed that. Thank you.…expand the range in sheet summary to measure quarterly time frames rather than monthly. Easy enough fix.

Cayla Davis

Here's the screenshot of the formula, i did have an IFERROR in there but i still get an error of #INCORRECT ARGUMENT SET

Paul Newcome

@Cayla Davis You are missing the last piece to the first IFERROR at the very end of your formula.

You have:

=IFERROR(AVG(COLLECT(………………..)))

You should have:

=IFERROR(AVG(COLLECT(………………..)), "whatever you want output if the whole formula errors")

Cayla Davis

I feel like I am blind… where would that - i did it here, but it still errors

=IFERROR(AVG(COLLECT({ResolutionDuration}, {ResolvedDate})), IFERROR(MONTH(@cell ), 0) >= 1, {ResolvedDate}, IFERROR(MONTH(@cell ), 0) <= 3, {ResolvedDate}, IFERROR(YEAR(@cell ), 0) = 2025))

Shaz

@Paul Newcome I didnot sound enough my point in previous post. We can wrap any error with iferror except #UNPARSEABLE and Circular referance and I agree.

Take a look at collect formula, it has range and criteria to check each cells of range. At first without wrapping iferror it will work.

When we add iferror, in place of comparing with each cell criteria becomes 'output of the iferror' >= 1. it doesnt compare against each cell. Collect formula gets a single value of true or false only, not the @cell reference. If the comparision is inside iferror then it will work.

Formula should be = AVG(COLLECT({ResolutionDuration}{ResolvedDate},AND(IFERROR(MONTH(@cell),0) >=1,IFERROR(MONTH(@cell),0)⇐3,IFERROR(YEAR(@cell),0=2025)))

Please note I am ignoring {ResolutionDuration},<>"" intentionally.

But simple formula using skipper logic is =AVG(COLLECT({ResolutionDuration},{ResolvedDate},AND(ISDATE(@cell),MONTH(@cell)>=1,MONTH(@cell)< =3,YEAR(@cell)=2025)))

@Cayla Davis Conside 5 sample resolution days 2, 2, 2, 2 and 15

Average is 4.6 doesnt stand close to any of the resolution duration but median is 2. Matches to most of it. This is why average itself not the right fit to ticket management. It pics how much it took most of time

Paul Newcome

@Shaz The IFERROR within such as when wrapping around the MONTH function does continue to compare on a cell by cell basis. I do this very frequently (multiple times each week).

What you say the formula should be is what I suggested with the exception of the outermost IFERROR. The outermost IFERROR mention in my formula is for when there are no rows to pull in and the #DIVIDE BY ZERO error presents itself. If there is at least one row being pulled in by the COLLECT function, it will not throw that error and will output the correct average.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.