Outlier Formula

Erick Gutierrez

I have a column labeled "Month Tenure" that has an outlier for of 215 which are the months that a staff member's been with the company, this staff member is on column labeled "Agents". Can someone please help me create a formula for this outlier?

Mark Cronk

Hi @Erick Gutierrez ,

=AVERAGEIF([month tenure]:[month tenure], <200) should do it. Change the 200 to whatever number you want to represent the lower limit of your outliers. As an example, if you want to exclude anyone with more than 50 months of tenure, change the 200 to 50.

Mark

MCorbin

It might be helpful to have a picture of your sheet, and more description of what you're looking for in the formula.

Erick Gutierrez

As you can see on the column that's labeled "Month Tenure" there's an outlier amount of month shown for agent Claudia Reyes, and it's 218 months, when most other agent have a tenure of single-double digit months. On the bottom I would like to remove the outlier number, on an Excel spreadsheet the formula is listed as: =((SUM(D:D)-(VLOOKUP("Claudia Reyes",B:D,3,FALSE)))/COUNTA(D:D))/12, but I have the slightest clue as how to do it on a Smartsheet. I hope this makes sense.

Mark Cronk

Hi @Erick Gutierrez ,

=AVERAGEIF([month tenure]:[month tenure], <200) should do it. Change the 200 to whatever number you want to represent the lower limit of your outliers. As an example, if you want to exclude anyone with more than 50 months of tenure, change the 200 to 50.

Mark