Calculate the MEDIAN and MODE from a list of numbers

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

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

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")