Finding the Mode Formula
Good Evening,
I have a few Likert scales that rank various variables from 1  5. I would like the helper column to show the number that is recorded the most often for that specific variable, i.e. the mode for each column. See example below of one Likert variable that has a 1, 2, and a few other numbers =>5. I would like the mode for this column to be out within the helper column.
Thanks,
NCNW Data
Best Answer

Good Afternoon KPH,
Thank you this formula is close to working however, I am receiving the prompt of #Circular Reference. I am new to smartsheets but I am familiar with this prompt in excel and usually I move the formula to a cell which does not include it in its' calculation.
I want to keep this formula within this helper column (Misinformation Rating) to determine the Mode within the Misinformation Rate Demand Column. How would I do this?
Thanks for your thoughts on this,
NCNWIncData
Answers

You could calculate the mode by creating a little table (could be in another sheet) and using a COUNTIF formula to count the occurrence of each rating:
The formula to count the number of occurrences of each rating is
=COUNTIF([Misinform Rate Demand]:[Misinform Rate Demand], Rating@row)
Then use an INDEX MATCH formula to return the rating that appears most often:
=INDEX(Rating:Rating, MATCH(MAX([Number of ratings]:[Number of ratings]), [Number of ratings]:[Number of ratings], 0))
If there is a tie, the first row will be returned. In my case, this is the lowest rating as I have the table in ascending order.

Good Afternoon KPH,
Thank you this formula is close to working however, I am receiving the prompt of #Circular Reference. I am new to smartsheets but I am familiar with this prompt in excel and usually I move the formula to a cell which does not include it in its' calculation.
I want to keep this formula within this helper column (Misinformation Rating) to determine the Mode within the Misinformation Rate Demand Column. How would I do this?
Thanks for your thoughts on this,
NCNWIncData

You cannot put the result of the formula in the Misinform Rating cell if the formula is using values in the Misinform Rating cell. It will be trying to calculate on itself, thus creating a circular reference.
If you want to use some rows in that column to display the result and some in the calculation, you could change the formula to use only certain rows rather than the entire column. If you wanted to only look at rows 5 to 50, you could change this
[Misinform Rate Demand]:[Misinform Rate Demand]
to
[Misinform Rate Demand]5:[Misinform Rate Demand]50
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!