# #UNPARSEABLE When Creating =AVERAGE(COLLECT Formula

Options
✭✭
edited 05/13/22

I am trying to pull an average of months employed for current employees in a specific division. My formula is:

=AVERAGE(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN"))

{MonthsEmp} is a column formula:

=ROUND((IF(ISDATE([Term Date]@row), IF(NETDAYS([Term Date]@row, TODAY()) > 0, NETDAYS([Hire Date]@row, [Term Date]@row)), NETDAYS([Hire Date]@row, TODAY())) / 365) * 12)

{TermYear} is a column formula:

=IF(ISBLANK([Term Date]@row), "Currently Employed", YEAR([Term Date]@row))

{DivAtTerm} is a text/Number column type

I'm getting the #UNPARSEABLE error for the Average(Collect formula and I cannot figure out why. I am still very new to Smartsheet.

Once I get this formula to work, I'll need the same formula but for employees where {TermYear} is not "CURRENTLY EMPLOYED".

I appreciate any help or ideas that anyone can provide!

(Edit to add: My original sheet with the column formulas above works just fine, I do not know if the issue with the Collect formula is due to the fact that the sheet reference is for columns with a column formulas. ?)

## Best Answer

• ✭✭✭✭✭✭
Answer ✓
Options

There is no AVERAGE function. Try replacing it with AVG.

## Answers

• ✭✭✭✭✭✭
edited 05/13/22
Options

Try changing the average to a join with a comma as a delimiter and see what is being pushed to the average from the collect

for the second one its the same formula with a small adjustment. replace "Currently Employed" with

not(@cell = "Currently Employed")

• ✭✭
Options

@L_123 I am still very new to Smartsheet, can you give me an example of what the formula you suggested should look like?

• ✭✭✭✭✭✭
Options

=AVERAGE(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN"))

changes to

=JOIN(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN"),", ")

for the second one its the same formula with a small adjustment. replace "Currently Employed" with

not(@cell = "Currently Employed")

• ✭✭
Options

@L_123 Using your 1st Join example results in: ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN

So, it's not collecting the months employed and then averaging, it is instead listing all currently employed where DivAtTerm is ADMIN.

• ✭✭✭✭✭✭
Options

Right, so that tells us that your issue is in the return column for the collect.

Check out your {MonthsEmp} reference and make sure it's pointing to the correct data

• ✭✭
Options

@L_123 You were correct! My {MonthsEmp} reference was calling the wrong column. With the join and the comma delimiter, it now returns a list of all of the # of months employed for current employees in the Admin Division. For Example: 12, 2, 6, 54, etc.

My original formula below (with the reference corrected) still returns #UNPARSEABLE:

=AVERAGE(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN"))

• ✭✭✭✭✭✭
Options

Make sure that your criterion range type for {TermYear} and {DivAtTerm} is text/number. If you are comparing date with text then it will error.

• ✭✭
Options

I just double checked the sheet being referenced and all requested columns are column type text/number...

• ✭✭✭✭✭✭
Answer ✓
Options

There is no AVERAGE function. Try replacing it with AVG.

• ✭✭
Options

@Paul Newcome YES! Thank you, this was the issue!

How do I add multiple cell values?

This is the formula that works:

=AVG(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN"))

Instead of TermYear, I want to use my {SeparationType} column with the values "Voluntary" and "Nonvoluntary".

• ✭✭✭✭✭✭
edited 05/19/22
Options

=AVG(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN"))

*lol i had this open in a different window, didn't see paul already got to it.

• ✭✭✭✭✭✭
Options

You would use the same syntax. Just adjust that particular range to the new range and adjust the criteria.

• ✭✭✭✭✭✭
Options

@L_123 I do that all the time. Hahaha

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!