Formula for averaging days

SASardy

I have two columns I am using for this formula: "Date Received" and "Days Duration (Received to Listed)". I want the formula below to look at any items in the Date Received column between 1/1/25 and 1/31/25 AND where there is a number in the Days Duration column (some columns have errors from time to time and I want to exclude those) and then I want the AVERAGE number of days. For example, if there are 3 entries with numbers (1, 2, and 3 days) and one that may have an #INVALID DATA TYPE, I want to return a 2. The formula AI returned is below and it keeps giving an #UNPARSEABLE error.

=AVERAGEIF([Date Received]:[Date Received], AND([Date Received]@cell >= DATE(2025, 1, 1), [Date Received]@cell <= DATE(2025, 1, 31), ISNUMBER([Days Duration (Received to Listed)]@cell)), [Days Duration (Received to Listed)]:[Days Duration (Received to Listed)])

Thanks in advance for any help.

Jgorsich

Odd, it worked on this side when I tested it.

Per Formula error messages | Smartsheet Learning Center:

#UNPARSEABLE
Cause
The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.

Resolution
Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").

Looking at the below the actual structure looks okay. So, I'd recommend 2 things - first, modify your "Days Duration (Received to Listed)" column formula to include an iferror() statement to force anything non-numeric to be text (I like just "", that way it is empty visually - but any text will work) instead of an error message. Error messages in referenced columns tend to break things and error traps are a good practice in general. Second, double check all your column names and types. This formula would expect the following column names and types:

Days Duration (Received to Listed) - Text/Number

Date Received - Date

=avg(collect([Days Duration (Received to Listed)]:[Days Duration (Received to Listed)],[Date Received]:[Date Received],>=date(2025,1,1),[Date Received]:[Date Received],<=date(2025,1,31),[Days Duration (Received to Listed)]:[Days Duration (Received to Listed)],>0)

Jgorsich

Averageif() only allows you to average a single range based upon criteria in another range - you can't have multiple criteria. I'm fairly certain you can't nest an and() inside of it either.

Functions List | Smartsheet Learning Center

If you structure it as =average(collect()) you'll likely have more luck. Collect() basically allows you to grab all data from a range based on criteria like an excel averageifs() or sumifs().

In your case, this would likely work:

=average(collect([Days Duration (Received to Listed)]:[Days Duration (Received to Listed)],[Date Received]:[Date Received],>=date(2025,1,1),[Date Received]:[Date Received],<=date(2025,1,31),[Days Duration (Received to Listed)]:[Days Duration (Received to Listed)],ISNUMBER([Days Duration (Received to Listed)]@cell))

if that doesn't work (I've not tested it), try changing the "isnumber" function to just ">0".

SASardy

@Jgorsich thank you for your input. I tried your formula as well as the ">0" tweak and both still return "#UNPARSEABLE"

Jgorsich

My mistake, change the "average" at the start to "avg".

SASardy

@Jgorsich I appreciate the follow up but still "#UNPARSEABLE"

Jgorsich

Odd, it worked on this side when I tested it.

Per Formula error messages | Smartsheet Learning Center:

#UNPARSEABLE
Cause
The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.

Resolution
Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").

Looking at the below the actual structure looks okay. So, I'd recommend 2 things - first, modify your "Days Duration (Received to Listed)" column formula to include an iferror() statement to force anything non-numeric to be text (I like just "", that way it is empty visually - but any text will work) instead of an error message. Error messages in referenced columns tend to break things and error traps are a good practice in general. Second, double check all your column names and types. This formula would expect the following column names and types:

Days Duration (Received to Listed) - Text/Number

Date Received - Date

=avg(collect([Days Duration (Received to Listed)]:[Days Duration (Received to Listed)],[Date Received]:[Date Received],>=date(2025,1,1),[Date Received]:[Date Received],<=date(2025,1,31),[Days Duration (Received to Listed)]:[Days Duration (Received to Listed)],>0)

SASardy

@Jgorsich After adding in the IFERROR, this worked. Thank you!

Jgorsich

Excellent, glad I could help!