#UNPARSEABLE When Creating =AVERAGE(COLLECT Formula

LFaraco · 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. ?)

Paul Newcome · 05/19/22

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

L_123 · 05/13/22

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")

LFaraco · 05/13/22

https://community.smartsheet.com/discussion/comment/329604#Comment_329604

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

L_123 · 05/13/22

=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")

LFaraco · 05/13/22

https://community.smartsheet.com/discussion/comment/329609#Comment_329609

@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.

L_123 · 05/13/22

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

LFaraco · 05/19/22

https://community.smartsheet.com/discussion/comment/329612#Comment_329612

@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"))

Sameer Karkhanis · 05/19/22

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.

LFaraco · 05/19/22

https://community.smartsheet.com/discussion/comment/330345#Comment_330345

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

Paul Newcome · 05/19/22

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

LFaraco · 05/19/22

https://community.smartsheet.com/discussion/comment/330363#Comment_330363

@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".

L_123 · 05/19/22

=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.

Paul Newcome · 05/19/22

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

Paul Newcome · 05/19/22

@L_123 I do that all the time. Hahaha

#UNPARSEABLE When Creating =AVERAGE(COLLECT Formula

Best Answer

Answers

Help Article Resources

Categories