#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Calculate the MEDIAN and MODE from a list of numbers

Hi there

Does anyone know the formula to caluculate the following (based on a list of numbers within a cell range)?

MEDIAN - The middle number when the numbers are put in order

MODE - Most frequently occuring number

Phil

Tags:

• Phil,

As far as I know, Smartsheet does not do this natively.

Since MEDIAN is not a calculation, it is more of an algorithm, I haven't come up with a satisfactory work-around.

I haven't needed MODE. If we are talking a known set of numbers, there might be a way to get that, but otherwise, it to is an algorithm.

Both could be done using the API (since that is coding, not formulas)

Craig

• Ok, thanks Craig

Phil

• You could export your numbers to excel and use the MEDIAN and MODE functions of Excel.

• TIm,

If you want to go to that trouble, I would just sync them via a different third party app.

Craig

• I know it's an old post, but figured I'd post for the next person searching.

The formula below can calculate Median, BUT it will only work if the range is sorted first!  Ascending or descending it doesn't matter; I've included a second more complex formula at the bottom that includes a rudimentary #SORTCHECK error if the largest and smallest values are not detected.  This isn't a foolproof test, just a pragmatic solution.

I'd suggest you temporarily rename the column containing your data to: Range before copying the formula into your sheet; Doing so will prevent you from needing to update all the Range:Range #REFs to your column name.   You can rename the column again after you have inserted the formula back to whatever you like.

Note: If your column contains other data, you will need to update each Range:Range #REF with the appropriate Range. IE: Range2:Range55 for rows 2-55. REMEMBER YOUR DATA MUST BE SORTED.

MEDIAN (Calculation Only)

=IF(IF(ROUND(COUNT(Range:Range) / 2) = COUNT(Range:Range) / 2, 1, 0), INDEX(Range:Range, ROUND(COUNT(Range:Range) / 2) + 1, 1) + ((INDEX(Range:Range, ROUND(COUNT(Range:Range) / 2), 1)) - (INDEX(Range:Range, ROUND(COUNT(Range:Range) / 2) + 1, 1))) / 2, INDEX(Range:Range, ROUND(COUNT(Range:Range) / 2), 1))

MEDIAN w/ SORT CHECK

=IF(IF(IF(AND(IF(INDEX(Range:Range, COUNT(Range:Range), 1) = LARGE(Range:Range, 1), 1, 0), (IF(INDEX(Range:Range, 1, 1) = SMALL(Range:Range, 1), 1, 0))), 1, 0) + IF(LARGE(Range:Range, 1) = INDEX(Range:Range, 1, 1), 1, 0) = 1, 1, 0), IF(IF(ROUND(COUNT(Range:Range) / 2) = COUNT(Range:Range) / 2, 1, 0), INDEX(Range:Range, ROUND(COUNT(Range:Range) / 2) + 1, 1) + ((INDEX(Range:Range, ROUND(COUNT(Range:Range) / 2), 1)) - (INDEX(Range:Range, ROUND(COUNT(Range:Range) / 2) + 1, 1))) / 2, INDEX(Range:Range, ROUND(COUNT(Range:Range) / 2), 1)), "#CHECKSORT")

This discussion has been closed.