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