Mode of Column (text)

jl.furstenberg

Hello,

I have seen that Smartsheet does not have a mode function similar to Excel, but I would love some help in figuring out a workaround. I have read other posts about workarounds, but have not had any luck getting it to work.

I currently have a lot of 5-point Likert survey data (across 60 "parent" surveys) and I want to get the mode for each column. My data is in text format (Strongly Disagree - Strongly Agree) and I have 6587 rows and counting.

How can I get the mode for each parent survey? I would truly appreciate any help.

Brian_Richardson

If you have fixed values for the data (it's always Strongly Disagree, Disagree, Neither Disagree or Agree, Agree, Strongly Agree) then you could do something like this:

=MAX(COUNTIF([Column Name]:[Column Name],"Strongly Disagree"),COUNTIF([Column Name]:[Column Name],"Disagree"),COUNTIF([Column Name]:[Column Name],"Neither Disagree or Agree"),COUNTIF([Column Name]:[Column Name],"Agree"),COUNTIF([Column Name]:[Column Name],"Strongly Agree"))

This formula counts the number of each response, then takes the MAX of those numbers to find the largest one - the most common one.

jl.furstenberg

Hello,

Thank you! I was able to use this to calculate the largest count for a column, but how can I get it to return which answer was the highest count? (i.e., "Agree" is the most frequent, it was xxxx times). Sorry for the questions, I am brand new to Smartsheet.

Jamie

Brian_Richardson

That is trickier :-)

To do you'll need to setup your data in a way to support a lookup of the value with the max count. I'm assuming you have a column for each question in your data in a layout like this:

If your layout is different you'll need to tweak this to work for your layout by changing up the ranges.

Either on the same sheet, or on a separate sheet, setup a table where you have the 5 values listed in a column, and then a column for each survey question set of results. So something like this:

In the Question 1 Count column, enter this formula and make it a column formula

=COUNTIF([Question 1]:[Question 1], Value@row)

Note if you are doing this on a separate sheet, then you'll want to use cross-sheet references to look something like this: =COUNTIF({Question 1}, Value@row). Setup cross-sheet references by clicking the "Reference other sheet" link while typing out this formula and select the column with the first question's responses in it from the other sheet.

That will give you a count of each answer in the Question 1 column of data, next to the value for that answer. Repeat this formula for Question 2 Count, etc, by adjusting the range each time.

Then on the MAX Value row, in each "Count" column, use this formula:

=INDEX(Value:Value, MATCH(MAX([Question 1 Count]:[Question 1 Count]), [Question 1 Count]:[Question 1 Count], 0))

This formula works like this:

1) Find the MAX count from the Question 1 Count column.

2) MATCH the MAX count to the numbers in the Question 1 Count column and return the row number

3) INDEX the Value text from the same row number

If it's all setup correctly, you should get something like this back;

Note that, if the counts are the same for 2 values, you'll get the first one back only.

jl.furstenberg

This is AWESOME! Thank you so much!!

jl.furstenberg

Last question, and I am not sure if it's possible. If I wanted to use the above formulas to calculate mode, but I wanted to reference children in a parent row instead of the entire column at once, could I insert count(children()) for each survey?

What I am trying to do is calculate the mode for each survey question by individual survey (e.g., Survey 1 mode for Q1 was this) and then calculate the mode for each question across all surveys (e.g., across all surveys the mode for Q1 was this). Several of the questions across all of our surveys are the exact same, so that's why I would like this info.

Brian_Richardson

I’m not sure exactly what your layout is but you can use Parent and Children functions to find parents and children. Google for the functions to find their pages. I’d also check out the Formula Handbook in the Solution Center (click + icon on Smartsheet left side navigation)

jl.furstenberg

Thank you for all your help Brian, much appreciated!! :)