Mode of Column (text)
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.
Answers
-
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.
-
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
-
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.
-
This is AWESOME! Thank you so much!!
-
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.
-
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)
-
Thank you for all your help Brian, much appreciated!! :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!