Calculate the MEDIAN and MODE from a list of numbers

Phil Smith

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

 

Thanks in advance

 

Phil

J. Craig Williams

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

 

 

 

Phil Smith

Ok, thanks Craig

 

Phil

Tim McCarthy

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

J. Craig Williams

TIm, 

 

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

 

Craig

Michael.Baker

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