Finding the Mode Formula

NCNWIncData

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

NCNWIncData

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

KPH

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.

NCNWIncData

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

KPH

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