Median of The Absolute Difference From the Median in an Array

[Jibrael]

Something like [=MEDIAN(ABS(C3:C20-MEDIAN(C3:C20)))] works but I can not find a way to define this statistic with a range of C:C so as to be able to see the change as soon as I add values?

I thought that [{=MEDIAN(ABS(IF(AND((C:C<>0),(ISNUMBER(C:C))),C:C)-MEDIAN(C:C)))}] Would exclude empty cells and text titles, but the correct value is not returned. If I do not use [C:C<>0] a large negative number is returned and it would suggest that every empty cell is being evaluated as "0-median"

Is there a way to define the first equation with a an entire column as a range and ignore empty cells and titles such that I can plug any data into the column and immediately obtain the MAD statistic?

Thank you for your time in consideration of this problem,

 

[Domenic]

First, convert your data into a Table (Ribbon > Insert > Table), and then specify the exact range. Then the range will automatically adjust as data is added or removed. Also, if the column contains empty cells, you can exclude them using the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=MEDIAN(IF(LEN(C3:C20)>0,ABS(C3:C20-MEDIAN(C3:C20))))

Adjust the range accordingly.

Hope this helps!