Mode Function

Shaq McWilliams

Does anyone know if there is a "Mode" formula yet without using a helper column?

StevenBlackburn

Still no MODE function yet but there are ways to do it:

https://help.smartsheet.com/functions

This functions list is incredibly helpful and I also suggest looking at the formula handbook if you haven't yet (super useful regardless).

There are many practical ways to get the MODE functionality though, just some workaround to do it (and its super quick to set up).

1. Insert helper column
2. Name it whatever you want, although it will be a Text/Number working on frequency
3. Count the times each value occurs with a COUNTIF formula (COUNTIF=($COLUMN1:$COLUMN1, COLUMN1@row)
   1. This formula needs to be adjusted to fit your column you are trying to count the frequencies on, and then counts those for you
   2. Think of this as — "My value is showing up N number of Times"
4. Now we want to pull the count with the highest frequency
   1. Using a sheet summary field or a blank cell (preferably Sheet Summary fields) write an index formula indexing that same column telling us the largest frequency count, which row that count is on, and looks to return that specific value based on the index for the column
   2. Formula would be something like this:
      1. =INDEX(COLUMN:COLUMN, MATCH(MAX(HELPERCOLUMN:HELPERCOLUMN), HELPERCOLUMN:HELPERCOLUMN, 0))
         1. Remember that the first column is your COLUMN1 column, and the frequency helper column you created is HELPERCOLUMN in this example
         2. This formula needs to be edited to fit your solution (it may need slight editing but should be close to what you need)

In summary, we find the biggest count, tackle that row and grab its corresponding value. There are other ways to do it, but this way is probably the most used and simplest way to get it implemented.

StevenBlackburn

Still no MODE function yet but there are ways to do it:

https://help.smartsheet.com/functions

This functions list is incredibly helpful and I also suggest looking at the formula handbook if you haven't yet (super useful regardless).

There are many practical ways to get the MODE functionality though, just some workaround to do it (and its super quick to set up).

1. Insert helper column
2. Name it whatever you want, although it will be a Text/Number working on frequency
3. Count the times each value occurs with a COUNTIF formula (COUNTIF=($COLUMN1:$COLUMN1, COLUMN1@row)
   1. This formula needs to be adjusted to fit your column you are trying to count the frequencies on, and then counts those for you
   2. Think of this as — "My value is showing up N number of Times"
4. Now we want to pull the count with the highest frequency
   1. Using a sheet summary field or a blank cell (preferably Sheet Summary fields) write an index formula indexing that same column telling us the largest frequency count, which row that count is on, and looks to return that specific value based on the index for the column
   2. Formula would be something like this:
      1. =INDEX(COLUMN:COLUMN, MATCH(MAX(HELPERCOLUMN:HELPERCOLUMN), HELPERCOLUMN:HELPERCOLUMN, 0))
         1. Remember that the first column is your COLUMN1 column, and the frequency helper column you created is HELPERCOLUMN in this example
         2. This formula needs to be edited to fit your solution (it may need slight editing but should be close to what you need)

In summary, we find the biggest count, tackle that row and grab its corresponding value. There are other ways to do it, but this way is probably the most used and simplest way to get it implemented.